Suppose there is a table:
| first | second | third |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 1 | 2 |
| 2 | 3 | 1 |
And by query:
SELECT first, count(*) AS cnt
FROM table
GROUP BY first
We get result:
| first | cnt |
|---|---|
| 1 | 4 |
| 2 | 1 |
How to calculate count of another column (columns) inside group? Expected result:
| first | cnt | aggregated_count_by_second | aggregated_count_by_third |
|---|---|---|---|
| 1 | 4 | {1: 3, 2: 1} | {1: 2, 2: 2} |
| 2 | 1 | {3: 1} | {1: 1} |
DB version: 10.3.39-MariaDB-0+deb10u1
