Can you add another row to a CASE statement that provides a TOTAL?

Question

I want to know if I can add a TOTAL count to the last row of my case statement:

SELECT 
    CASE
        WHEN weight <= 10 THEN "very small"
        WHEN weight > 10 AND weight <= 30 THEN "small"
        WHEN weight > 30 AND weight <= 50 THEN "medium"
        WHEN weight > 50 AND weight <= 85 THEN "large"
        WHEN weight > 85 THEN "very large"
        END AS dog_weights, count(weight) as count
FROM dogs
GROUP BY dog_weights;

Table from Output

| Dog_Weights | Count |
|-------------|-------|
| Very Small  | 20    |
| Small       | 20    |
| Medium      | 40    |
| Large       | 20    |

Desired Table

| Dog_Weights | Count |
|-------------|-------|
| Very Small  | 20    |
| Small       | 20    |
| Medium      | 40    |
| Large       | 20    |
| Total       | 100   |

Would I use a sub-query for this? Thanks.

Possible duplicate of https://stackoverflow.com/questions/17934318/add-a-summary-row-with-totals ? — Celius Stingher, Sep 18 '19 at 17:59

Gordon Linoff · Answer 1 · 2019-09-18T18:56:13.493

The simplest method in MySQL is to use rollup:

SELECT (CASE WHEN weight <= 10 THEN 'very small'
             WHEN weight > 10 AND weight <= 30 THEN 'small'
             WHEN weight > 30 AND weight <= 50 THEN 'medium'
             WHEN weight > 50 AND weight <= 85 THEN 'large'
             WHEN weight > 85 THEN 'very large'
         END) AS dog_weights,
        count(*) as count
FROM dogs
GROUP BY dog_weights WITH rollup;

To get TOTAL, I think you need a subquery:

Assuming that weight is never NULL, you can do:

SELECT COALESCE(dog_weights, 'total') as dog_weights, count
FROM (SELECT (CASE WHEN weight <= 10 THEN 'very small'
                   WHEN weight > 10 AND weight <= 30 THEN 'small'
                   WHEN weight > 30 AND weight <= 50 THEN 'medium'
                   WHEN weight > 50 AND weight <= 85 THEN 'large'
                   WHEN weight > 85 THEN 'very large'
               END) AS dog_weights,
              count(*) as count
      FROM dogs
      GROUP BY dog_weights WITH rollup
     ) d

Can you add another row to a CASE statement that provides a TOTAL?

1 Answers1