How about this?
SELECT col_date, SUM(COUNT)
FROM (
SELECT ukdate, COUNT(*) COUNT
FROM tomtest
GROUP BY ukdate
UNION
SELECT ADD_MONTHS(TO_DATE('12012002','mmddyyyy'),ROWNUM) dba_month,0
FROM DBA_OBJECTS
WHERE ROWNUM < 13)
GROUP BY ukdate
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, October 29, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L
Same idea as what Iain suggests, dreadful implementation :
SQL> select trunc(ukdate) ukdate, count(*) from test
2 group by trunc(ukdate);
UKDATE COUNT(*)
---------- ----------
01/01/2003 5
02/01/2003 6
04/01/2003 6
SQL> get x
1 select y.full_ukdate ukdate,
2 nvl(x.cnt, 0) "COUNT(*)"
3 from (select trunc(ukdate) ukdate,
4 count(*) cnt
5 from test
6 group by trunc(ukdate)) x,
7 (select a.rn + b.mindate - 1 full_ukdate
8 from (select rownum rn
9 from all_tab_columns) a,
10 (select min(ukdate) mindate,
11 max(ukdate) maxdate
12 from test) b
13 where a.rn <= b.maxdate - b.mindate + 1) y
14* where x.ukdate (+) = y.full_ukdate
SQL> /
UKDATE COUNT(*)
---------- ----------
01/01/2003 5
02/01/2003 6
03/01/2003 0
04/01/2003 6
Do you _really_ want that :-) ?
Didn't find analytical functions of much help on this one ...
SF
>----- ------- Original Message ------- -----
>From: "Nicoll, Iain" <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Wed, 29 Oct 2003 04:44:25
>
>You colud try joining to an in-line view something
>like
>
>SELECT :XDATE+(ROWNUM-1) DDATE
>FROM DBA_OBJECTS
>WHERE ROWNUM <= (:YDATE - :xdate)+1
>
>where dba_objects could be any table with enough
>rows to ensure you always
>covered the complete range.
>
>
>
>-----Original Message-----
>Aidan Whitehall
>Sent: 29 October 2003 10:49
>To: Multiple recipients of list ORACLE-L
>
>
>This is probably a no-brainer...
>
>We have some date-based data for which most days
>have several records
>but where some days have none. I'm COUNT()ing the
>number of records for
>each day (between day x and day y) and need a
>record set that also
>includes a row for those days which have no
>records:
>
>UkDate Total
>1/1/2003 5
>2/1/2003 6
>3/1/2003 0
>4/1/2003 6
>
>I could post-process the record set to achieve
>this, but is there any
>way in 9i to do an aggregate query with an outer
>join on a date range
>(if that makes sense)?
>
>Someone made the suggestion of creating another
>table with a row for
>every day under the sun in it, against which you
>could inner join the
>main query, but I'm not keen on that (that is just
>a gut response
>though).
>
>Any ideas? Thanks!
>
>--
>Aidan Whitehall
><mailto:[EMAIL PROTECTED]>
>Macromedia ColdFusion Developer
>Fairbanks Environmental Ltd +44 (0)1695 51775
>Queen's Awards Winner 2003
><http://www.fairbanks.co.uk/go/awards>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?utf-8?B?TWVyY2FkYW50ZSwgVGhvbWFzIEY=?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
