conditional aggregates

Date: April 21st 2016
Last updated: April 21st 2016

Conditional aggregates include IF ELSE logic. However, SQL uses CASE, WHEN, ELSE, THEN, and END.

Count all boards with volume less than 28 litres

select boardmake, 
        COUNT(CASE 
               WHEN boardvolume < 28.0 
               THEN 1 
               ELSE NULL 
               END) AS vol 
        FROM surfboards 
        GROUP BY boardmake;
/*+---------------------------+-----+
| boardmake                 | vol |
+---------------------------+-----+
| Chilli Surfboards         |  12 |
| DHD Surfboards            |  14 |
| Hectic                    |   0 |
| Lost Surfboards           |  20 |
| MR Surfboards             |   0 |
| Rusty Surfboards          |  12 |
| Simon Anderson Surfboards |  10 |
| Superbrand Surfboards     |  19 |
+---------------------------+-----+
8 rows in set (0.00 sec)

Sum total volume in litres of Chilli surfboards

select boardmake, 
       SUM(boardvolume), 
       SUM(CASE WHEN 
           boardmake = 'Chilli Surfboards' 
           THEN boardvolume 
           ELSE 0 
           END) AS vol 
       FROM surfboards 
       GROUP BY boardmake;
/*+---------------------------+------------------+---------+
| boardmake                 | SUM(boardvolume) | vol     |
+---------------------------+------------------+---------+
| Chilli Surfboards         |          1110.68 | 1110.68 |
| DHD Surfboards            |          1664.83 |    0.00 |
| Hectic                    |            28.40 |    0.00 |
| Lost Surfboards           |          2336.08 |    0.00 |
| MR Surfboards             |           376.20 |    0.00 |
| Rusty Surfboards          |          1223.85 |    0.00 |
| Simon Anderson Surfboards |          1273.16 |    0.00 |
| Superbrand Surfboards     |           986.84 |    0.00 |
+---------------------------+------------------+---------+
8 rows in set (0.00 sec)

Find the percentage of boards under 28 litres for each manufacturer

select boardmake, 
        COUNT(boardmake) AS count, 
        100* SUM(CASE WHEN 
                  boardvolume <= 28 
                  THEN 1 
                  ELSE 0 
                  END)/
                  COUNT(boardmake) AS 
                  percent_boards_under_28_litres 
         FROM surfboards 
         GROUP BY boardmake;
/*+---------------------------+-------+--------------------------------+
| boardmake                 | count | percent_boards_under_28_litres |
+---------------------------+-------+--------------------------------+
| Chilli Surfboards         |    36 |                        33.3333 |
| DHD Surfboards            |    53 |                        26.4151 |
| Hectic                    |     1 |                         0.0000 |
| Lost Surfboards           |    72 |                        27.7778 |
| MR Surfboards             |     9 |                         0.0000 |
| Rusty Surfboards          |    39 |                        30.7692 |
| Simon Anderson Surfboards |    40 |                        25.0000 |
| Superbrand Surfboards     |    36 |                        52.7778 |
+---------------------------+-------+--------------------------------+