0

Is it possible to sum all row which has been summed?

For instance I have this query:

   SELECT SUM(registered) AS `totalOftheDay`
   FROM table
   WHERE DATE(ds.register_time) BETWEEN '2015-03-01 00:00:00' AND '2015-03-30 23:59:59'
   GROUP BY DATE(ds.register_time)

What I need is when total of registered people has been summed by date (group by) then I want to sum it again base on the month. So I got two results: based on date and based on month. How to do that if it is possible to do that in sql query?

Thanks for the help. Cheers!

For example:

    +-----------------------+----------------+
      register_time             registered
    +-----------------------+----------------+
      2015-03-01 01.00.00           20      
      2015-03-01 02.00.13           40      
      2015-03-01 03.01.10           60      
      2015-03-01 04.20.00           80      
      2015-03-01 05.01.10           20      
      2015-03-01 13.00.00           20      
     ----------------------------------------TOTALOFTHEDAY SUM 240
      2015-03-02 13.00.00           20      
      2015-03-02 14.00.00           20      
      2015-03-02 15.00.00           20      
     ----------------------------------------TOTALOFTHEDAY SUM 60
      2015-03-03 13.00.01           20      
      2015-03-03 14.00.02           20      
      2015-03-03 15.00.03           20      
      2015-03-03 16.00.04           20      
      2015-03-03 17.00.05           20      
     ----------------------------------------TOTALOFTHEDAY SUM 100

After I summed the total registered user by date, then I want to sum it again:

    240 + 60 + 100 = 400 (TOTALOFTHEMOTHN)
  • can you specify the exact output required with example – ughai Mar 31 '15 at 3:50
  • I update the question with the exact output – Al Kasih Mar 31 '15 at 3:58
1

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
|improve this answer|||||
  • MySQL has ROLLUP but please correct the WITH and GETDATE(). ' It doesn't have CTEs and the date functions are different to SQL-Server's. – ypercubeᵀᴹ Mar 31 '15 at 7:29
  • @ypercube - Thanks for pointing it out, I missed the tag for mysql. updated the answer – ughai Mar 31 '15 at 7:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.