Mysql – Get only overall summary WITH ROLLUP and GROUP BY for multiple fields

group byMySQL

Performing a WITH ROLLUP when grouping by multiple fields, MySQL returns a rollup row for each group, as well as the overall summary:

CREATE TABLE test (name VARCHAR(50), number TINYINT);
INSERT INTO test VALUES
    ('foo', 1), ('foo', 1), ('foo', 2), ('foo', 3), ('foo', 3),
    ('bar', 1), ('bar', 2), ('bar', 2), ('bar', 2), ('bar', 3),
    ('baz', 1), ('baz', 2), ('bar', 2);
SELECT name, number, COUNT(1) FROM test GROUP BY name, number WITH ROLLUP;

+------+--------+----------+
| name | number | count(1) |
+------+--------+----------+
| bar  |      1 |        1 |
| bar  |      2 |        3 |
| bar  |      3 |        1 |
| bar  |   NULL |        5 |
| baz  |      1 |        1 |
| baz  |      2 |        2 |
| baz  |   NULL |        3 |
| foo  |      1 |        2 |
| foo  |      2 |        1 |
| foo  |      3 |        2 |
| foo  |   NULL |        5 |
| NULL |   NULL |       13 |
+------+--------+----------+

I'm not inerested in the rollups for foo/bar/baz, only the overall summary. What's the most efficient way to achieve this?

Best Answer

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE (ISNULL(name) + ISNULL(number)) <> 1;

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE ISNULL(name) = ISNULL(number);

Related Solutions

MySQL – Using Multiple COUNT Fields with GROUP BY

I removed one of the subqueries from yours but fundamentally as mysql doesn't support count over partition etc, it will need more than one pass through the table:

Select test.mid, test.pid, A.cnt as midCount, count(*) as pidCount
from test join
(Select mid, count(*) as cnt from test group by mid) A
on test.mid =  A.mid
Group by mid, pid

Mysql – Group only certain rows with GROUP BY

First, do not use either of your two queries. Both have a group by some column (GROUP BY group_id) and then select other columns, non-aggregated (SELECT id, name). This may give you wrong and unexpected results, despite that it may work in your tests, with some small sized table.

Second, the UNION ALL is not a problem. If the two subqueries perform efficiently, then the final union is ok, too. If you need a sort, the efficiency will depend on how that sort differs from the indexes used.

Now, the problem of "groupwise-max" or "greatest-n-per-group" has many solutions (and even a tag, both at SO and here). There are two sub-problems, depending on whether ties can happen and what the wanted results are in those cases.

If you want all the tied rows, the solution with GROUP BY inside a derived table is usually good. In your case, that you want just one row returned per group, another approach is easier to write and usually performs very well when there is a small number of group overall:

SELECT id, name, price 
FROM items
WHERE group_id IS NULL

UNION ALL

SELECT i.id, i.name, i.price 
FROM 
    ( SELECT DISTINCT group_id 
      FROM items
      WHERE group_id IS NOT NULL
    ) AS di
  JOIN 
    items AS i
  ON  i.id = 
    ( SELECT id
      FROM items 
      WHERE group_id = di.group_id
      ORDER BY price, id             -- order for resolving ties
      LIMIT 1
    ) 
ORDER BY
    <some_columns> ;                 -- final order

An index on (group_id, price, id) will be helpful

Best Answer

Related Solutions

MySQL – Using Multiple COUNT Fields with GROUP BY

Mysql – Group only certain rows with GROUP BY

Related Question