1

Possible Duplicate:
Calculate a running total in MySQL

I need to get the sum of counts which is grouped for each of the dates.Now I am running the following query and getting the out put as follows :

SELECT  `timestamp` , COUNT( * ) 
FROM  `A` 
WHERE  `timestamp` >=  '2013-01-04 07:12:12'
GROUP BY DATE_FORMAT( `timestamp` ,  '%Y-%m-%d' )

and I am getting

OUTPUT:

timestamp                                               count(*)
-------------------------------------------------- -----------
2013-01-04 07:58:21                                4
2013-01-05 09:28:56                                38
2013-01-06 00:03:04                                10

Now what I need is, I need to get the total sum of the counts grouped by date. That is for the second date it should be 42 and for third date it should be 52. How can I do this in a query?

Community
  • 1
  • 1
Happy Coder
  • 4,255
  • 13
  • 75
  • 152

4 Answers4

0

Can you try below SQL

SELECT DATE_FORMAT( ts_date ,  '%Y-%m-%d' ) as ts_dt_out, SUM(cnt) 
FROM
(
SELECT  `timestamp` ts_date , COUNT( * )  as cnt
FROM  `A` 
WHERE  `timestamp` >=  '2013-01-04 07:12:12'
GROUP BY DATE_FORMAT( `timestamp` ,  '%Y-%m-%d' ) 
)
as inner
WHERE ts_date >=  '2013-01-04 07:12:12'
GROUP BY ts_dt_out

Note: Not tested let me know if it does not work

Minesh
  • 2,284
  • 1
  • 14
  • 22
  • this might work for this case, but what if there is going to be more rows, what if for 10 more days, your solution is limited for 3 days as Alwin mentioned, I hope he needs a dynamic query as a solution – hsuk Jan 31 '13 at 10:06
0

Give it a try:

SELECT  `timestamp` , @sum:= ifnull(@sum, 0 ) + COUNT( * ) 
FROM  `A` 
WHERE  `timestamp` >=  '2013-01-04 07:12:12'
GROUP BY DATE_FORMAT( `timestamp` ,  '%Y-%m-%d' )
palindrom
  • 18,033
  • 1
  • 21
  • 37
0

Try :

SELECT 
   timestamp t , 
   (select count(*) from A where  timestamp <= t)
FROM  A
GROUP  BY timestamp
ORDER  BY timestamp
hsuk
  • 6,770
  • 13
  • 50
  • 80
0

Here is a simple way

SELECT  
    `timestamp` , 
    COUNT( * ) 
FROM  `A` 
WHERE  `timestamp` >=  '2013-01-04 07:12:12'
GROUP BY DATE(`timestamp`)
Muhammad Raheel
  • 19,823
  • 7
  • 67
  • 103