Re: Help needed for select query

On Fri, 26 Oct 2001 15:26:51 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
><library.treasures_at_NOSPAMsaqnet.co.uk> wrote in message
>news:3bd95661.15061363_at_news.easynet.co.uk...
>> I have an event table with a source column and a time colum.
>>
>> I need to count and sort in descending order the number of events per
>> source.
>>
>> I can count one day's totals and the last week's totals by two
>> separate scripts:
>>
>> SELECT source, COUNT(*) "Yesterday"
>> FROM event
>> WHERE time > TRUNC(SYSDATE-1)
>> GROUP BY source
>> ORDER BY COUNT(*) DESC, source;
>>
>> SELECT source, COUNT(*) "Last week"
>> FROM event
>> WHERE time > TRUNC(SYSDATE-7)
>> GROUP BY source
>> ORDER BY COUNT(*) DESC, source;
>>
>>
>> However, I would like to unify these two separate queries in a single
>> query because I want the two column, "Yesterday" and "Last week", to
>> appear side by side.
>>
>> How is it possible to achieve this?
>>
>> Thanks for any help in advance.
>>
>> Thomas
>
> sum(decode(trunc(time),trunc(sysdate),1,0) "Yesterday"
>, sum(decode(trunc(time),trunc(sysdate),0,1) "Last week"
>
>Hth
>
>Sybrand Bakker
>Senior Oracle DBA

Thank you very much, Sybrand.

As I am not an experienced SQL user, I assume these lines must come after "SELECT", that is, they are to be selected also.

As far as I managed to experiment, these bring result, but that is different from the results I get when using my two separate queries alone.

It could be because for the time periods,

time > TRUNC(SYSDATE-1)
and
time > TRUNC(SYSDATE-7),

I simply allowed these periods to include some time of today, up to now.

I will need to study and understand more, though :-)

Regards,
Thomas Received on Sat Oct 27 2001 - 13:40:24 CDT