CODEWITHSUNDEEP
mysqlsql
dynsne
dynsne

Reputation: 341

How to create a cumulative sum grouped by column

I have a table with a two or more columns representing states

╔════════════╤═══════════╤═══════════╗
║ updated_at │ state_one │ state_two ║
╠════════════╪═══════════╪═══════════╣
║ 12/31/1999 │ 1         │ 2         ║
╟────────────┼───────────┼───────────╢
║ 1/1/2000   │ 2         │ 3         ║
╟────────────┼───────────┼───────────╢
║ 1/2/2000   │ 0         │ 3         ║
╚════════════╧═══════════╧═══════════╝

I'd like to be able write a simple query to calculate the cumulative sum of each row that's in a given state for both state columns state_one and state_two. A query that gives me something like:

╔════════════╤═══════════╤═══════════╤══════════════════════╤══════════════════════╗
║ updated_at │ state_one │ state_two │ cumulative_sum_one_1 │ cumulative_sum_two_2 ║
╠════════════╪═══════════╪═══════════╪══════════════════════╪══════════════════════╣
║ 12/31/1999 │ 1         │ 2         │ 1                    │ 1                    ║
╟────────────┼───────────┼───────────┼──────────────────────┼──────────────────────╢
║ 1/1/2000   │ 2         │ 2         │ 1                    │ 2                    ║
╟────────────┼───────────┼───────────┼──────────────────────┼──────────────────────╢
║ 1/2/2000   │ 0         │ 1         │ 1                    │ 2                    ║
╚════════════╧═══════════╧═══════════╧══════════════════════╧══════════════════════╝

There'd be more columns, but there'd be more columns since there a are a few more states.

I'm working with MySQL version 5.6.35. While I know I'm doing it completely wrong, this is the query I have so far but it calculates the cumulative sum for all rows:

select
    row.day,
    case row.state when 1 then "foo"
                   when 2 then "bar"
                   else "baz"
    end as state,
    row.state_count,
    @running_total:= (
        @running_total + row.state_count
     ) as cumulative_sum
from (
    select
        date_format(from_unixtime(updated_at), '%m/%d/%Y') as day,
        count(state) as state_count,
        state
    from 
        table_of_interest
    group by day
) row
join (select @running_total:=0) r
order by row.day

Upvotes: 1

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use correlated subqueries:

select t.*,
       (select count(*)
        from table_of_interest t2
        where t2.update_at <= t.updated_at and t2.state_one = 1
       ) as cumulative_sum_one_1,
       (select count(*)
        from table_of_interest t2
        where t2.update_at <= t.updated_at and t2.state_one = 2
       ) as cumulative_sum_two_2
from table_of_interest t;

Upvotes: 1

Related Questions