Table with sum of values by period of time...

plimster

I have built the following table. The output shows the sum of nic values by period of time.

Can anyone recommend an alternative way of doing this using less code?

mysql> select IFNULL(hb, 'Grand Total') as hb,

    -> SUM(CASE WHEN period like '%201701%' THEN nic ELSE 0 END) AS NIC_201701,

    -> SUM(CASE WHEN period like '%201702%' THEN nic ELSE 0 END) AS NIC_201702,

    -> SUM(CASE WHEN period like '%201703%' THEN nic ELSE 0 END) AS NIC_201703,

    -> SUM(CASE WHEN period like '%201704%' THEN nic ELSE 0 END) AS NIC_201704,

    -> SUM(CASE WHEN period like '%201705%' THEN nic ELSE 0 END) AS NIC_201705,

    -> SUM(CASE WHEN period like '%201706%' THEN nic ELSE 0 END) AS NIC_201706

    -> from gpdata group by hb with rollup;

+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

| hb          | NIC_201701  | NIC_201702  | NIC_201703  | NIC_201704  | NIC_201705  | NIC_201706  |

+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

|

7A1        |  9821653.91 |  9296426.44 | 10771850.39 |  9355558.98 | 10297467.67 |  9941474.09 |

|

7A2        |  6255069.28 |  5810429.93 |  6727811.30 |  5945101.83 |  6391336.89 |  6788546.91 |

|

7A3        |  8319853.53 |  7764051.43 |  9221905.37 |  7863964.80 |  8630539.85 |  9146829.49 |

|

7A4        |  6563452.65 |  6187291.71 |  7171467.12 |  6203596.09 |  6866615.83 |  7093624.07 |

|

7A5        |  4761829.73 |  4577696.66 |  5381511.27 |  4611417.00 |  5060302.77 |  5323103.20 |

|

7A6        |  8590549.83 |  8089842.56 |  9415392.99 |  8195376.65 |  8918646.64 |  9464744.69 |

|

7A7        |  2036398.75 |  1970752.06 |  2226818.78 |  2009392.94 |  2117409.51 |  2252211.53 |

| Grand Total | 46348807.68 | 43696490.79 | 50916757.22 | 44184408.29 | 48282319.16 | 50010533.98 |

+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

8 rows in set, 1 warning (44.61 sec)

mysql> describe gpdata;

+------------+--------------+------+-----+---------+-------+

| Field      | Type         | Null | Key | Default | Extra |

+------------+--------------+------+-----+---------+-------+

| hb         | varchar(8)   | YES  |     | NULL    |       |

| locality   | varchar(8)   | YES  |     | NULL    |       |

| practiceid | varchar(8)   | YES  |     | NULL    |       |

| bnfcode    | varchar(30)  | YES  |     | NULL    |       |

| bnfname    | varchar(60)  | YES  |     | NULL    |       |

| items      | int(11)      | YES  |     | NULL    |       |

| nic        | decimal(9,2) | YES  |     | NULL    |       |

| actcost    | decimal(9,2) | YES  |     | NULL    |       |

| quantity   | int(11)      | YES  |     | NULL    |       |

| ddd        | decimal(9,2) | YES  |     | NULL    |       |

| adq        | decimal(9,2) | YES  |     | NULL    |       |

| period     | varchar(8)   | YES  |     | NULL    |       |

+------------+--------------+------+-----+---------+-------+

12 rows in set (0.05 sec)

mysql>

Note: This has been built as a demo using Open Government Data to supplement by Curriculum Vitae.

Gaz in Oz

To be honest, CASE expression is the way to go...

Any way, Here's a "shorter" version, i.e. less characters:

SELECT IFNULL(hb, 'Grand Total') AS hb,       SUM(IF(period LIKE '%201701%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201702%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201703%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201704%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201705%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201706%', nic, 0)) AS nic_201701,FROM gpdata GROUP BY hb WITH ROLLUP;

Gaz in Oz

To be honest, CASE expression is the way to go...

Any way, Here's a "shorter" version, i.e. less characters:

SELECT IFNULL(hb, 'Grand Total') AS hb,       SUM(IF(period LIKE '%201701%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201702%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201703%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201704%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201705%', nic, 0)) AS nic_201701,       SUM(IF(period LIKE '%201706%', nic, 0)) AS nic_201701,FROM gpdata GROUP BY hb WITH ROLLUP;

plimster

I guess this will use less characters LOL! Many thanks!

Gaz in Oz

You're welcome...