CODEWITHSUNDEEP
databaseoracle-databasecount
M Zal
M Zal

Reputation: 31

Count by days, with all days

I need to count records by days, even if in the day were no records.

Count by days, sure, easy.

But how i can make it to print information, that 'in day 2018-01-10 was 0 records)

Should I use connect by level? Please, any help would be good. Can't use plsql, just oracle sql

Upvotes: 0

Views: 44

Answers (1)

Patrick H
Patrick H

Reputation: 653

First you generate every date that you want in an inline view. I chose every date for the current year because you didn't specify. Then you left outer join on date using whichever date field you have in that table. If you count on a non-null field from the source table then it will count 0 rows on days where there is no join.

select Dates.r, count(tablename.id)
from (select trunc(sysdate,'YYYY') + level - 1 R
        from dual
      connect by level <= trunc(add_months(sysdate,12),'YYYY') - trunc(sysdate,'YYYY')) Dates
left join tablename
  on trunc(tablename.datefield) = Dates.r
group by Dates.r

Upvotes: 1

Related Questions