How to build pivot table in CnosDB?

Question

I want to get more aggregated data in one result set. I have tried function GROUP BY CUBE but it did not work as I expected.

Here is my data set:

❯ CREATE TABLE tbl(f_1 DOUBLE, TAGS(room, device));

❯ INSERT INTO tbl(time, room, device, f_1) VALUES
('2023-12-01T00:00:00', 'A', 'mt', 57),
('2023-12-01T00:00:00', 'A', 'f', 70),
('2023-12-01T00:00:00', 'B', 'mt', 58),
('2023-12-01T00:00:00', 'B', 'f', 71),
('2023-12-01T00:01:00', 'A', 'mt', 57),
('2023-12-01T00:01:00', 'A', 'f', 70),
('2023-12-01T00:01:00', 'B', 'mt', 71);

❯ SELECT * FROM tbl ORDER BY time, room, device;
+---------------------+------+--------+------+
| time                | room | device | f_1  |
+---------------------+------+--------+------+
| 2023-12-01T00:00:00 | A    | f      | 70.0 |
| 2023-12-01T00:00:00 | A    | mt     | 57.0 |
| 2023-12-01T00:00:00 | B    | f      | 71.0 |
| 2023-12-01T00:00:00 | B    | mt     | 58.0 |
| 2023-12-01T00:01:00 | A    | f      | 70.0 |
| 2023-12-01T00:01:00 | A    | mt     | 57.0 |
| 2023-12-01T00:01:00 | B    | mt     | 71.0 |
+---------------------+------+--------+------+

Here is my SQL and the result set:

❯ SELECT COALESCE(room, 'all_room') AS t#room, COALESCE(device, 'all_device') as t#device, COUNT(*) as t#count FROM tbl GROUP BY CUBE(room, device) ORDER BY room, device;
+----------+------------+---------+
| t#room   | t#device   | t#count |
+----------+------------+---------+
| A        | f          | 1       |
| A        | mt         | 1       |
| A        | all_device | 2       |
| B        | f          | 1       |
| B        | mt         | 1       |
| B        | all_device | 1       |
| B        | all_device | 1       |
| all_room | f          | 1       |
| all_room | f          | 1       |
| all_room | mt         | 1       |
| all_room | mt         | 1       |
| all_room | all_device | 2       |
| all_room | all_device | 1       |
| all_room | all_device | 1       |
+----------+------------+---------+

Seems something went wrong, what I expected is a pivot table like this:

+----------+------------+---------+
| t#room   | t#device   | t#count |
+----------+------------+---------+
| A        | f          | 2       |
| A        | mt         | 2       |
| A        | all_device | 4       |
| B        | f          | 1       |
| B        | mt         | 2       |
| B        | all_device | 3       |
| all_room | f          | 3       |
| all_room | mt         | 4       |
| all_room | all_device | 7       |
+----------+------------+---------+

Answer 1

Finally it worked after I edited the agg function from count(*) to count(f_1):

❯ SELECT COALESCE(room, 'all_room') AS t#room, COALESCE(device, 'all_device') as t#device, COUNT(f_1) as t#count FROM tbl GROUP BY CUBE(room, device) ORDER BY room, device;
+----------+------------+---------+
| t#room   | t#device   | t#count |
+----------+------------+---------+
| A        | f          | 2       |
| A        | mt         | 2       |
| A        | all_device | 4       |
| B        | f          | 1       |
| B        | mt         | 2       |
| B        | all_device | 3       |
| all_room | f          | 3       |
| all_room | mt         | 4       |
| all_room | all_device | 7       |
+----------+------------+---------+