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','mmdd'),ROWNUM) dba_month,0
FROM DBA_OBJECTS
WHERE ROWNUM < 13)
GROUP BY ukdate
Tom Mercadante
Oracle Certified Professiona
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,