I have the following table (see it on SQL Fiddle) (I created to break down my problem):
| ID | word |
----------------
| 5 | "Hello" |
| 6 | NULL |
| 7 | "World" |
| 8 | "World" |
Now I want to count the number of occurrences of each word using GROUP BY word WITH ROLLUP
. The NULL in the column word of the row generated by the ROLLUP should be replaced by "total":
SELECT
ID,
ifnull(word, "total") as word,
count(*) as occurrences
FROM test
GROUP BY word WITH ROLLUP;
The problem is that it also replaces the NULL
in the record with the amount of rows where words is NULL:
| ID | word | occurrences |
|----|-------|-------------|
| 6 | total | 1 | <- Here lies the problem
| 5 | Hello | 1 |
| 7 | world | 2 |
| 7 | total | 4 |
So I now use count(word)
to distinguish whether it's a NULL from my data or it's a NULL created by ROLLUP so I can replace it with either "empty" or "total":
SELECT
ID,
if(count(word) = 0, "empty", ifnull(word, "total")) as word,
count(*) as occurrences
FROM test
group by word with ROLLUP;
This works for my use case but it still has a flaw:
If word is NULL in all rows, count(word)
is also 0 in the final row showing the total: (SQL Fiddle with different data (only record#6))
| ID | word | occurrences |
|----|-------|-------------|
| 6 | empty | 1 |
| 6 | empty | 1 | <- word should be "total"
The following statement delivers the result I want:
SELECT
ID,
ifnull(word, "total") as word,
count(*) as occurrences
FROM
(
SELECT
ID,
ifnull(word, "empty") as word
FROM test
) tmp
GROUP BY word WITH ROLLUP
But since in most answers around here the use of subqueries seems to be discouraged, I am wondering if there is a better solution.
GROUPING
function to disambiguate this case. Doesn't look like that exists in MySQL. – Martin Smith Jul 9 '16 at 14:56GROUPING()
was added in MySQL 8.0. – Rick James Mar 17 '20 at 3:48