Using the MySQL query below, I have created a pivot table which is pretty much exactly what I am looking for. I would however like to replace the NULL values with actual descriptions, such as SubTotal and GrandTotal. Here is the pivot table format as displayed on my PHP output (hopefully the formatting is somewhat legible!).
Name Division 1 Division 2 Division 3 Division 4 Location
Name 1
Name 2
Name 3
NULL Total Total Total Total
Name 4
Name 5
NULL Total etc
NULL Column Grand Total
Here is the query I used to generate the table. After researching this issue, it seems like the CASE function is the way to go. However, when I add the two CASE lines into the query below, it doesn't seem to want to work. The mysql_error that returns says that the "GROUPING function does not exist".
SELECT
CASE WHEN (GROUPING(name)=1) THEN 'MainTotal' ELSE name END AS name,
CASE WHEN (GROUPING(location)=1) THEN 'SubTotal' ELSE location END AS location,
name AS Name,
SUM(IF(division='OEM',totalHours,NULL)) AS OEM,
SUM(IF(division='A/M',totalHours,NULL)) AS AM,
SUM(IF(division='SKF',totalHours,NULL)) AS SKF,
SUM(IF(division='RE',totalHours,NULL)) AS RE,
location as Location
FROM $databasetable GROUP BY location, name
WITH ROLLUP
Can anybody tell me what I'm doing wrong? Is the CASE function the way to go to replace the NULL category titles?
Thanks in advance!
Try it like this:
SELECT
IFNULL(name, 'MainTotal') AS name,
IFNULL(location, 'SubTotal') AS location,
SUM(IF(division='OEM',totalHours,NULL)) AS OEM,
SUM(IF(division='A/M',totalHours,NULL)) AS AM,
SUM(IF(division='SKF',totalHours,NULL)) AS SKF,
SUM(IF(division='RE',totalHours,NULL)) AS RE,
location as Location
FROM $databasetable GROUP BY location, name
WITH ROLLUP
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With