I have a simple group by query:
SELECT timestamp, COUNT(users)
FROM my_table
GROUP BY users
How do I add a sum_each_day column that will sum the users count of each row and will aggregate it forward to the next row and so on
The output should be like this:
timestamp | users | sum_each_day
2015-11-27 1 1
2015-11-28 5 6
2015-11-29 3 9
2015-11-30 7 16
Thanks in advance
You could use a sub-query, like this:
SELECT timestamp,
num_users,
(SELECT COUNT(users)
FROM my_table
WHERE timestamp <= main.timestamp) sum_users
FROM (
SELECT timestamp,
COUNT(users) num_users
FROM my_table
GROUP BY timestamp
) main
from_unixtime(mytimestamp, '%Y %D %M')COUNT(users) and SUM(users)
area is numeric and you want the sum per timestamp, then add SUM(area) sum_area next to COUNT(users) num_users, and select sum_area at the top. The principle is similar with max.If you really need this in mysql it'll cost some performance but i believe a sub query with a count will solve it:
SELECT t1.timestamp, count (), select count () from my_table t2 where t2.timestamp <= t1.timestamp From my_table t1 Group by users
If you display this data through a scripting language like PHP it would be easier to keep a counter and display the aggregate per row.
I would do this using variable:
SET @total := 0;
SELECT timestamp, DayCount, (@total := @total + DayCount) AS Total
FROM
(SELECT timestamp, COUNT(users) AS DayCount
FROM my_table
GROUP BY timestamp) AS t1
Fiddler: I am not using your table structure here, but you can get idea
If I understand correclty, this will work:
set @c=0;
SELECT `timestamp`,sum(`users`),(select @c:=@c+sum(`users`))
FROM `my_table`
group by `timestamp`;
group byon a column that is being aggregated in theselectlist, while the column that is not aggregated is not listed in thegroup by. Neither is allowed in standardsql. Makes it hard to understand, and I wonder if it is intended.