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

Question

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!

score 20 · Accepted Answer · answered Jul 25 '11 at 21:45

20

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

answered Jul 25 '11 at 21:45

Joe Stefanelli

132,803
19
237
235

That was perfect. Thanks Joe! That seems like it's much simpler as well, which works for me! – Andrew Jul 26 '11 at 01:12
What version of `MySQL` implements `GROUPING`? – PM 77-1 Oct 09 '13 at 16:45
1

Lets say you have rows, where location IS NULL, then you'll have a NULL result in your ROLLUP. In this case I add a COUNT(DISTINCT groupedcolum) AS variants to separate between the totals row (will have a count of > 1) and an actual NULL row (will have a count of 0) – allanlaal Oct 26 '16 at 22:18

score 2 · Answer 2 · answered Aug 02 '16 at 16:48

I know this question is pretty old at this point, but the result is still the first returned when I search Google for 'mysql with rollup labels'.

Your response uses the grouping() function which isn't part of MySQL, but there is a similar way to solve your issue.

I assume you are trying to avoid adjusting your output from the query following execution.

Try the following:

SELECT
CASE
  WHEN [column1 grouped by] IS NULL THEN "Total"
  ELSE [column1]
END AS `[alias]`,
CASE
  WHEN [column1 grouped by] IS NULL THEN "---"
  ELSE [column2]
END AS `[alias2]`,
SUM(IF([condition1] = [variable], [sum column], 0)) as `[alias3]`
...
FROM [table]
GROUP BY [column1] WITH ROLLUP

The logic here replaces the null values returned for the grouped by column, which occurs for the final row when using ROLLUP. Any additional columns that are not aggregated by the rollup default to the last row returned by your query, excluding the rollup totals. To avoid this you use a case to check if the grouped by column is null, and replace the chosen column value as whatever you want it to be via the case return.

If you need to do subtotals, use the same logic but set up separate instances of the query and UNION them together to form your pivot table.

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

2 Answers2