mysql FUNCTION GROUPING does not exist

Question

When using WITH ROLLUP I receive this error:

#1305 - FUNCTION GROUPING does not exist

My DB version:

innodb_version  5.7.28
version     10.2.30-MariaDB

Strange that WITH ROLLUP works, but not GROUPING(). I would like to replace the nulls

Any idea?

My SQL:

SELECT
IF(GROUPING(`Categorie`), 'All Categorie',`Categorie`) AS `Categorie`,
`Grootboek`,
SUM(`Bedrag2020`) AS `Bedrag2020` 
FROM `vw_resultaat_ytd` 
Group by
`Categorie`,
`Grootboek` WITH ROLLUP;

As described in: https://www.mysqltutorial.org/mysql-rollup/

Answer 1

Clearly, grouping() is not supported. Unless Categorie can be NULL, then just use COALESCE():

SELECT COALESCE(Categorie, 'All Categorie') AS Categorie, 
       Grootboek, SUM(Bedrag2020) AS Bedrag2020
FROM vw_resultaat_ytd 
GROUP BY Categorie, Grootboek WITH ROLLUP;

If Categorie can be NULL, then you need to replace the value with an invalid value and undo that:

SELECT (CASE WHEN Categorie IS NULL THEN 'All Categorie'
             WHEN Categorie <> '<NULL>' THEN Categorie
        END)  AS Categorie, 
       Grootboek, SUM(Bedrag2020) AS Bedrag2020
FROM vw_resultaat_ytd 
GROUP BY COALESCE(Categorie, '<NULL>'), Grootboek WITH ROLLUP;

Answer 2

Great Gordon, that help me out. Finally this was the end solution I was looking for, but I did not fully explain it:

SELECT  Categorie, 
COALESCE(Grootboek, concat('All ', Categorie)) AS Grootboek,
SUM(Bedrag2020) AS Bedrag2020
FROM vw_resultaat_ytd 
GROUP BY Categorie, Grootboek WITH ROLLUP;