MySQL query to generate frequency counts for multiple columns

Question

I have a table in MySQL with three columns that hold the time duration for different processes:

A     B     C
--------------
1     3     5
1     6     3
4     7     6
2     4     3

I'd like to query to get the frequency count for all 3 process times (so I can eventually compare them in a histogram), like so:

seconds     A     B     C
--------------------------
1           2     0     0
2           1     0     0
3           0     1     2
4           1     1     0
5           0     0     1
6           0     1     1
7           0     1     0

So far, the solution I've come up with is to make three separate tables that perform count(*) and then merge them, but I feel like there must be a cleaner way.

Answer 1

You can use union all and aggregation:

select
    sec,
    sum(tab = 'a') a,
    sum(tab = 'b') b,
    sum(tab = 'c') c
from (
    select 'a' tab, a sec from mytable
    union all select 'b', b from mytable
    union all select 'c', c from mytable
) t
group by sec
order by sec

Demo on DB Fiddle:

sec |  a |  b |  c
--: | -: | -: | -:
  1 |  2 |  0 |  0
  2 |  1 |  0 |  0
  3 |  0 |  1 |  2
  4 |  1 |  1 |  0
  5 |  0 |  0 |  1
  6 |  0 |  1 |  1
  7 |  0 |  1 |  0