Home » SQL & PL/SQL » SQL & PL/SQL » SQL rollup count every day
SQL rollup count every day [message #669390] Thu, 19 April 2018 10:29 Go to next message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
Below is the sample code for illustration:
with val (id,dt) as (  
select 1,   TO_TIMESTAMP('2017/03/22 10:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 2,   TO_TIMESTAMP('2017/03/22 10:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 3,   TO_TIMESTAMP('2017/03/22 12:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 4,   TO_TIMESTAMP('2017/03/22 12:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 5,   TO_TIMESTAMP('2017/03/22 15:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 6,   TO_TIMESTAMP('2017/03/23 10:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 7,   TO_TIMESTAMP('2017/03/23 11:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 8,   TO_TIMESTAMP('2017/03/23 11:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 9,   TO_TIMESTAMP('2017/03/23 14:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all  
select 10,  TO_TIMESTAMP('2017/03/23 14:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual   
) select nvl(to_char(trunc(dt, 'HH'), 'DD-MON-RR HH24'), 'SUM =   ') AS hourly_date,count(id) from val  
group by rollup (trunc(dt, 'HH'))  
order by 1 

Below is the output for above SQL:
Quote:

HOURLY_DATE COUNT(ID)
------------ ----------
22-MAR-17 10 2
22-MAR-17 12 2
22-MAR-17 15 1
23-MAR-17 10 1
23-MAR-17 11 2
23-MAR-17 14 2
SUM = 10

Is there any way to produce Totals for each day, rather than grand total
Re: SQL rollup count every day [message #669391 is a reply to message #669390] Thu, 19 April 2018 10:43 Go to previous message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4139005
Previous Topic: Dropping a Primary Key constraint
Next Topic: Need Help In Dates
Goto Forum:
  


Current Time: Wed Jan 29 01:23:48 CST 2020