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.

https://stackoverflow.com/questions/2179207/sum-values-of-a-single-row — Zeljka, Dec 18 '19 at 13:15

score 2 · Accepted Answer · answered Dec 18 '19 at 13:15

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

MySQL query to generate frequency counts for multiple columns

1 Answers1