Home -> Usenet -> comp.databases.oracle -> HELP... Oracle SQL Date question...

HELP... Oracle SQL Date question...

From: keith kwiatek <kkwiatek_at_nist.gov>
Date: 1999/01/10
Message-ID: <77a8f5$g10$1_at_news.nist.gov>#1/1 






Hello Smart One!



I have an oracle table called "orders", that records the date a user
completed an on-line purchase...



I can write a query that will do a "group by" to show all the dates and the
total number of orders for each date...that is simple:



SELECT to_char(order_date,'DD-MON-YY'), count(order_id)
FROM orders


GROUP BY to_char(order_date,'DD-MON-YY')



and it will generate something like:


1-JAN-99  34
2-JAN-99  55
4-JAN-99  41






BUT, notice in the above example that on 3-JAN-99 there were no orders! And
indeed, there are no records in the ORDERS table for that date...SO, how do
I write a query that will print out '03-JAN-99   0' , instead of just
skippng the date altogether?



In other words, when there are no records for a given date (03-JAN-99), I
want the SQL to print 0 (zero) instead of skipping the date (it must do this
across months and years):


1-JAN-99  34
2-JAN-99  55
3-JAN-99   0
4-JAN-99  41







|Thanks!


Please email me at kkwiatek_at_nist.gov



Regards,


Keith
Received on Sun Jan 10 1999 - 00:00:00 CET












Original text of this message