MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (
	id INT AUTO_INCREMENT PRIMARY KEY,
	initial CHAR(1),
	data VARCHAR(255)
);
INSERT INTO test(initial,data)
VALUES
	('a','apple'), ('b','banana'), ('c','cherry'), ('a','aardvark'), ('c','cockatoo'),
	('a','accordion'), ('b','banjo'), ('c','cor anglais'), ('d','dobro'), ('d','dromedary'),
	('d','date'), ('e','elderberry'), ('e','euphonium'), ('f','fleugel horn'), ('f','fig'),
	('e','elephant'), ('g','giraffe');

Records: 17  Duplicates: 0  Warnings: 0


SELECT initial, data, count(*)
FROM test
GROUP BY initial,data WITH ROLLUP;


initial
data
count(*)


a
aardvark
1

a
accordion
1

a
apple
1

a
null
3

b
banana
1

b
banjo
1

b
null
2

c
cherry
1

c
cockatoo
1

c
cor anglais
1

c
null
3

d
date
1

d
dobro
1

d
dromedary
1

d
null
3

e
elderberry
1

e
elephant
1

e
euphonium
1

e
null
3

f
fig
1

f
fleugel horn
1

f
null
2

g
giraffe
1

g
null
1

null
null
17


SELECT initial, data, count(*)
FROM test
GROUP BY initial,data WITH ROLLUP
ORDER BY grouping(initial),initial,grouping(data),data;

Incorrect usage of CUBE/ROLLUP and ORDER BY

initial	data	count(*)
a	aardvark	1
a	accordion	1
a	apple	1
a	null	3
b	banana	1
b	banjo	1
b	null	2
c	cherry	1
c	cockatoo	1
c	cor anglais	1
c	null	3
d	date	1
d	dobro	1
d	dromedary	1
d	null	3
e	elderberry	1
e	elephant	1
e	euphonium	1
e	null	3
f	fig	1
f	fleugel horn	1
f	null	2
g	giraffe	1
g	null	1
null	null	17