Geek Answers Handbook

Cannot sum the amount of `COUNT (*)`

In the second section, this answer uses variables to create the sum of another column. I do the same thing, except that I use a statement GROUP BYand sum COUNT(*)instead of a column. Here is my code for creating a minimal table and inserting values:

CREATE TABLE `test_group_cumulative` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `group_id` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test_group_cumulative` (`id`, `group_id`)
VALUES
    (1, 1),
    (2, 2),
    (3, 3);

And here is the code that fails:

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`
GROUP BY `group_id`
ORDER BY `id`;

Here is the result:

group_id    count   count_cumulative
1   1   1
2   1   1
3   1   1

As you can see, it count_cumulativedoes NOT stack correctly. However, the strange part is here. If I replaced COUNT(*)in count_cumulativewith this value 1, the request will work correctly.

    @count_cumulative := @count_cumulative + 1 AS `count_cumulative`

Here is the correct result:

group_id    count   count_cumulative
1   1   1
2   1   2
3   1   3

, , COUNT(*) 1. , , , , , , . COUNT(*) ?

+4
2

, . - . , , .

sqlfiddle, : http://sqlfiddle.com/#!2/cc97e/21

, :

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

:

GROUP_ID    CUM_CNT
1           1
2           2
3           3

, :

, mysql , , , 0.

:

SELECT @count_cumulative;


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`
GROUP BY `group_id`
ORDER BY `id`;

1. @count_cumulative reset 0.

, , " ", .

+1

@Ashalynd, count (*) . , :

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

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

   GROUP_ID    GROUPCOUNT    COUNTER
  ------------------------------------     
   1           1             0
   2           1             0
   3           1             0

2.
    SELECT @COUNTER;

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

    @COUNTER
    --------
    1

0. , COUNT (*) .

, :

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

-- RESULT
============
   GROUP_ID    GROUPCOUNT    COUNTER
  ------------------------------------     
   1           1             1
   2           1             2
   3           1             3

2.    
SELECT @COUNTER;

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

    @COUNTER
    --------
    3

1. .

+2

More articles:


All Articles