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/

Please show us your query and explain what you are trying to do. — GMB, Mar 02 '20 at 09:49
GROUPING is not implemented in MariaDB. [SELECT WITH ROLLUP](https://mariadb.com/kb/en/select-with-rollup/). — Akina, Mar 02 '20 at 09:54
MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4 is a replacements for MySQL 5.7 the grouping() function is related to mysql 8.0.12 .. this mean that in your mariadb version the grouping function is not available — ScaisEdge, Mar 02 '20 at 10:04

score 2 · Answer 1 · answered Mar 02 '20 at 12:17

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;

Frits Nagtegaal · Answer 2 · 2020-03-03T12:59:58.863

1

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;

edited Mar 03 '20 at 12:59

answered Mar 02 '20 at 16:16

Frits Nagtegaal

61
1
6

mysql FUNCTION GROUPING does not exist

2 Answers2