How can I replace the category names with NULL in the MySQL ROLLUP function?

Question

Using the following MySQL query, I created a pivot table that exactly matches what I'm looking for. However, I would like to replace the NULL values ​​with actual descriptions such as SubTotal and GrandTotal. Here is the pivot table form 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 that I used to create the table. After exploring this issue, it seems that the CASE function is the way to go. However, when I add two CASE lines to the query below, it does not seem to want to work. The returned mysql_error 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 someone tell me what I'm doing wrong? Is the CASE function a way to replace NULL category names?

Thanks in advance!

Answer 1

Try the following:

 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 

Answer 2

I know this question is pretty old at the moment, but the result is still the first when I searched google for "mysql with swap labels".

Your answer uses the grouping() function, which is not part of MySQL, but there is a similar way to solve your problem.

I assume that you are trying to avoid adjusting the output from the request after 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 

Here, the logic replaces the null values ​​returned for grouped by column, which are found for the last row when using ROLLUP . Any additional columns that are not aggregated by default for the last row returned by your query, excluding swap totals. To avoid this, you use the case to check if the grouped by column is zero, and replace the selected column value as you want it to be through case return.

If you need to make subtotals, use the same logic, but configure individual query instances and UNION them together to form a pivot table.