CODEWITHSUNDEEP
mysql
Andreluiz
Andreluiz

Reputation: 55

Sum rows from different conditions in Mysql

The original table is large so I will simplify it:

mytable:

CONDITION SIZE 
1          10
9          10
9          10
1          20
9          20
1          20
1          30

With a query similar to

SELECT
  CASE WHEN CONDITION=1 THEN 'OK' ELSE 'BAD' END AS Status,
  SUM (CASE WHEN SIZE=10 THEN 1 ELSE 0 END) AS Small,
  SUM (CASE WHEN SIZE=20 THEN 1 ELSE 0 END) AS Medium,
  SUM (CASE WHEN SIZE=30 THEN 1 ELSE 0 END) AS Large,
FROM mytable GROUP BY Status

Then we have this result

Status    Small    Medium    Large
OK         1         2         1
BAD        2         1         0

What is the proper code to get:

Status    Small    Medium    Large
OK         1         2         1
BAD        2         1         0
TOTAL      3         3         1

Upvotes: 0

Views: 71

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

You can add a WITH ROLLUP clause to your GROUP BY condition like this:

SELECT
  CASE WHEN CONDITION=1 THEN 'OK' ELSE 'BAD' END AS Status,
  SUM (CASE WHEN SIZE=10 THEN 1 ELSE 0 END) AS Small,
  SUM (CASE WHEN SIZE=20 THEN 1 ELSE 0 END) AS Medium,
  SUM (CASE WHEN SIZE=30 THEN 1 ELSE 0 END) AS Large,
FROM mytable
GROUP BY Status WITH ROLLUP

This would yield a result set like:

Status    Small    Medium    Large
OK         1         2         1
BAD        2         1         0
[NULL]     3         3         1

You would need to understand the behavior that there would not be any Total value in the Status column. Instead the Status column would have a NULL value indicating that this is where the rollup is made.

For more information you can read the documentation here: http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html

Upvotes: 2

Karl Kieninger
Karl Kieninger

Reputation: 9129

If I understand correctly you need union in a total query since you cannot aggregate in different ways at the same time.

SELECT
  CASE WHEN CONDITION=1 THEN 'OK' ELSE 'BAD' END AS Status,
  SUM (CASE WHEN SIZE=10 THEN 1 ELSE 0 END) AS Small,
  SUM (CASE WHEN SIZE=20 THEN 1 ELSE 0 END) AS Medium,
  SUM (CASE WHEN SIZE=30 THEN 1 ELSE 0 END) AS Large,
FROM mytable GROUP BY Status

UNION

SELECT
  'Total' AS Status,
  SUM (CASE WHEN SIZE=10 THEN 1 ELSE 0 END) AS Small,
  SUM (CASE WHEN SIZE=20 THEN 1 ELSE 0 END) AS Medium,
  SUM (CASE WHEN SIZE=30 THEN 1 ELSE 0 END) AS Large,
FROM mytable

Upvotes: 0

Related Questions