Andrew Andrew - 4 months ago 67
MySQL Question

How can I replace NULL category titles in MySQL ROLLUP function?

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!

Answer

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