Skip to Main Content
  • Questions

  • Select Query for Oracle DB when no records found on specific date using group by

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajas.

Asked: March 24, 2016 - 10:14 am UTC

Last updated: March 24, 2016 - 11:01 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

Hi Tom,
I want a query to show the number of records from say date 1-Jan-2016 to 29-Feb-2016 by grouping these date wise. Also, if say on 4-Jan-2016 no data was recorded it should show the same with count=0. Could you please help me with this as I have been struggling with this data for sometime.

and Chris said...

To include dates with no entries in your table, you'll need to outer join to a dates table.

This could be a real table or you can generate one:

with dates as (
  select date'2016-01-01'+rownum-1 dt from dual
  connect by level <= date'2016-02-29' - date'2016-01-01' + 1
)
  select dt, count(*) from dates
  left  join your_table
  on    dt = trunc(your_table.dt)
  group by dt;

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Other Options

Rajeshwaran Jeyabal, March 24, 2016 - 1:27 pm UTC 

rajesh@ORA11G> variable b1 varchar2(20);
rajesh@ORA11G> variable b2 varchar2(20);
rajesh@ORA11G> exec :b1 := '01-Jan-2013';

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec :b2 := '31-dec-2013';

PL/SQL procedure successfully completed.

rajesh@ORA11G>
rajesh@ORA11G> select trunc(created,'mm') dt,count(*) cnt
  2  from all_users
  3  where created between to_date(:b1,'dd-mon-yyyy')
  4     and  to_date(:b2,'dd-mon-yyyy')
  5  group by trunc(created,'mm')
  6  order by 1 ;

DT                 CNT
----------- ----------
01-OCT-2013         30

1 row selected.

rajesh@ORA11G> select *
  2  from (
  3  select trunc(created,'mm') dt,count(*) cnt
  4  from all_users
  5  where created between to_date(:b1,'dd-mon-yyyy')
  6     and  to_date(:b2,'dd-mon-yyyy')
  7  group by trunc(created,'mm')
  8       )
  9  model
 10    dimension by (dt)
 11    measures( cnt )
 12    rules
 13    ( cnt[ for dt from to_date(:b1,'dd-mon-yyyy')
 14               to to_date(:b2,'dd-mon-yyyy')
 15           increment numtoyminterval(1,'month') ] = nvl(cnt[cv()] ,0) )
 16  order by 1
 17  /

DT                 CNT
----------- ----------
01-JAN-2013          0
01-FEB-2013          0
01-MAR-2013          0
01-APR-2013          0
01-MAY-2013          0
01-JUN-2013          0
01-JUL-2013          0
01-AUG-2013          0
01-SEP-2013          0
01-OCT-2013         30
01-NOV-2013          0
01-DEC-2013          0

12 rows selected.

rajesh@ORA11G>

A reader, April 28, 2016 - 7:27 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.