Return to Answer

deleted 86 characters in body

Source Link

edited Mar 31, 2015 at 7:38

you can use the WITH ROLLUP in the group by to achieve multi level aggregation. try something like this SQL Fiddle

;WITH table1create astable table1
(
SELECT GETDATE() register_time,10 date,registered
UNION ALLint  
SELECT);

insert GETDATEinto table1 values('2015/03/31',10);
insert register_timeinto table1 values('2015/03/31',20 registered);
UNIONinsert ALLinto 
SELECTtable1 GETDATEvalues()-2 register_time'2015/03/21',30 registered);
UNIONinsert ALLinto 
SELECTtable1 GETDATEvalues()-2 register_time'2015/02/01',15 registered);
UNIONinsert ALLinto 
SELECTtable1 GETDATEvalues()+10 register_time'2015/02/01',1 registered);
UNIONinsert ALLinto 
SELECTtable1 GETDATEvalues()+10 register_time'2015/02/02',25 registered);
UNIONinsert ALLinto 
SELECTtable1 GETDATEvalues()+50 register_time'2015/01/31',11 registered
);


SELECT MONTH(ds.register_time) Month,DAY(ds.register_time) day,SUM(registered) AS totalOftheDay
  FROM table1 ds
  WHERE ds.register_time BETWEEN '2015-03-01 00:00:00' AND '2015-12-30 23:59:59'
  GROUP BY MONTH(ds.register_time),DAY(ds.register_time) WITH ROLLUP

you can use the WITH ROLLUP in the group by to achieve multi level aggregation. try something like this

;WITH table1 as 
(
SELECT GETDATE() register_time,10 registered
UNION ALL 
SELECT GETDATE() register_time,20 registered
UNION ALL 
SELECT GETDATE()-2 register_time,30 registered
UNION ALL 
SELECT GETDATE()-2 register_time,15 registered
UNION ALL 
SELECT GETDATE()+10 register_time,1 registered
UNION ALL 
SELECT GETDATE()+10 register_time,25 registered
UNION ALL 
SELECT GETDATE()+50 register_time,11 registered
)
SELECT MONTH(ds.register_time) Month,DAY(ds.register_time) day,SUM(registered) AS totalOftheDay
  FROM table1 ds
  WHERE ds.register_time BETWEEN '2015-03-01 00:00:00' AND '2015-12-30 23:59:59'
  GROUP BY MONTH(ds.register_time),DAY(ds.register_time) WITH ROLLUP

you can use the WITH ROLLUP in the group by to achieve multi level aggregation. try something like this SQL Fiddle

create table table1
(
register_time date,registered int  
);

insert into table1 values('2015/03/31',10);
insert into table1 values('2015/03/31',20);
insert into table1 values('2015/03/21',30);
insert into table1 values('2015/02/01',15);
insert into table1 values('2015/02/01',1);
insert into table1 values('2015/02/02',25);
insert into table1 values('2015/01/31',11);


SELECT MONTH(ds.register_time) Month,DAY(ds.register_time) day,SUM(registered) AS totalOftheDay
  FROM table1 ds
  GROUP BY MONTH(ds.register_time),DAY(ds.register_time) WITH ROLLUP

Source Link

created Mar 31, 2015 at 4:10

ughai

you can use the WITH ROLLUP in the group by to achieve multi level aggregation. try something like this

;WITH table1 as 
(
SELECT GETDATE() register_time,10 registered
UNION ALL 
SELECT GETDATE() register_time,20 registered
UNION ALL 
SELECT GETDATE()-2 register_time,30 registered
UNION ALL 
SELECT GETDATE()-2 register_time,15 registered
UNION ALL 
SELECT GETDATE()+10 register_time,1 registered
UNION ALL 
SELECT GETDATE()+10 register_time,25 registered
UNION ALL 
SELECT GETDATE()+50 register_time,11 registered
)
SELECT MONTH(ds.register_time) Month,DAY(ds.register_time) day,SUM(registered) AS totalOftheDay
  FROM table1 ds
  WHERE ds.register_time BETWEEN '2015-03-01 00:00:00' AND '2015-12-30 23:59:59'
  GROUP BY MONTH(ds.register_time),DAY(ds.register_time) WITH ROLLUP