Cannot Cumulatively Sum `count(*)`

The second section of this answer uses variables to create a cumulative sum of another column. I'm doing the same thing, except that I am using a GROUP BY statement, and summing CO

Solution 1:

I agree with @Ashalynd, the value of count(*) is not evaluated yet. Here is a little experiment I did :

1.SELECT
        GROUP_ID,    
        @COUNTER :=@COUNTER+COUNT(*)  GROUPCOUNT,
        @COUNTER COUNTER
     FROM
        TEST_GROUP_CUMULATIVE, 
        (SELECT@COUNTER :=0) R
    GROUPBY
        GROUP_ID;

-- RESULT============

   GROUP_ID    GROUPCOUNT    COUNTER
  ------------------------------------     1102103102.SELECT@COUNTER;

    -- RESULT=============@COUNTER--------1

For each group the variable is being initialized as 0. This means COUNT(*) has not been evaluated yet.

Also, when you do:

1.SELECT
        GROUP_ID,    
        @COUNTER :=@COUNTER+1  GROUPCOUNT,
        @COUNTER COUNTER
     FROM
        TEST_GROUP_CUMULATIVE, 
        (SELECT@COUNTER :=0) R
    GROUPBY
        GROUP_ID;

-- RESULT============
   GROUP_ID    GROUPCOUNT    COUNTER
  ------------------------------------     1112123132.SELECT@COUNTER;

    -- RESULT=============@COUNTER--------3

It does not have to evaluate 1. It directly sums it up and it gives you the cumulative sum.

Solution 2:

This is a problem I often face when doing time series analysis. My preferred way to tackle this is to wrap it into a second select and introduce the counter in the last layer. And you can adapt this technique to more complicated data flows using temporary tables, if reqiured.

I did this small sqlfiddle using the schema you present: http://sqlfiddle.com/#!2/cc97e/21

And here is the query to get the cumulative count:

SELECT
tgc.group_id, @count_cumulative := @count_cumulative + cnt as cum_cnt
FROM (
  SELECT
    group_id, COUNT(*) AS cnt
  FROM `test_group_cumulative` 
  groupby group_id
  orderby id) AS `tgc`, 
(SELECT @count_cumulative := 0) AS `temp_var`; 

This is the result I get:

GROUP_ID    CUM_CNT
1           1
2           2
3           3

The reason your attempt did not work:

When you do a group by with the temporary variable, mysql executes individual groups independently, and at the time each group is assigned the temporary variable current value, which in this case is 0.

If, you ran this query:

SELECT @count_cumulative;

immediately after

SELECT
    `group_id`,
    COUNT(*) AS `count`,
    @count_cumulative := @count_cumulative + COUNT(*) AS `count_cumulative`
FROM `test_group_cumulative` AS `tgc`
JOIN (SELECT @count_cumulative := 0) AS `_count_cumulative`
GROUPBY `group_id`
ORDERBY `id`;

you would get the value 1. For each of your groups, the @count_cumulative is being reset to 0.

Hence, in my proposed solution, I circumvent this issue by generating the 'group-counts' first and then doing the accumulation.