| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Date-based query Q
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:aidanwhitehall_at_fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775
Queen's Awards Winner 2003 <http://www.fairbanks.co.uk/go/awards>
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: ListGuru_at_fatcity.com (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). Received on Wed Oct 29 2003 - 04:49:26 CST
 |
 |