My data looks something like that. I want to get a table that shows the frequency of each number in each column.

The furthest I have gotten is

select A, count(*)
from table
group by A
order by A asc;

This only gives me the first two columns of this table.

CodePudding user response：

SELECT `Value`,
   (select count(*) from `table` where A = `Value`) as `Freq in A`,
   (select count(*) from `table` where B = `Value`) as `Freq in B`,
   (select count(*) from `table` where C = `Value`) as `Freq in C`,
FROM (
    select distinct A as `Value` from `table`
    union
    select distinct B from `table`
    union 
    select distinct C from `table`
) t
GROUP BY `Value`

CodePudding user response：

The following uses a cross join and group by to determine the frequency in each. The first query assumes that not all desired values i.e. 1-9 are present in either of the 3 columns in the table. The second query assumes that these values must occur atleast 1 and performs a union similar to JoelCoehoorn's answer. A case expression is used to check whether the values are present in the column before it is aggregated using count.

Query #1

SELECT
    Val,
    COUNT(
        CASE WHEN v.Val=tb.A THEN 1 END
    ) as `Frequency in A`,
    COUNT(
        CASE WHEN v.Val=tb.B THEN 1 END
    ) as `Frequency in B`,
    COUNT(
        CASE WHEN v.Val=tb.C THEN 1 END
    ) as `Frequency in C`
FROM (
     SELECT 1 as Val
     UNION ALL
     SELECT 2
     UNION ALL
     SELECT 3
     UNION ALL
     SELECT 4
     UNION ALL
     SELECT 5
     UNION ALL
     SELECT 6
     UNION ALL
     SELECT 7
     UNION ALL
     SELECT 8
     UNION ALL
     SELECT 9
) v
CROSS JOIN tb
GROUP BY Val
ORDER BY Val;

Query #2

SELECT
    Val,
    COUNT(
        CASE WHEN v.Val=tb.A THEN 1 END
    ) as `Frequency in A`,
    COUNT(
        CASE WHEN v.Val=tb.B THEN 1 END
    ) as `Frequency in B`,
    COUNT(
        CASE WHEN v.Val=tb.C THEN 1 END
    ) as `Frequency in C`
FROM (
  SELECT DISTINCT Val FROM (
     SELECT A as Val FROM tb UNION ALL
     SELECT B FROM tb UNION ALL
     SELECT C FROM tb
  ) v1
) v
CROSS JOIN tb
GROUP BY Val
ORDER BY Val;

Query #3

Uses recursive CTE for newer mysql versions.

WITH RECURSIVE numbers as (
    SELECT 1 as Val
    UNION ALL
    SELECT Val 1 FROM numbers WHERE Val < 9
)
SELECT
    Val,
    COUNT(
        CASE WHEN v.Val=tb.A THEN 1 END
    ) as `Frequency in A`,
    COUNT(
        CASE WHEN v.Val=tb.B THEN 1 END
    ) as `Frequency in B`,
    COUNT(
        CASE WHEN v.Val=tb.C THEN 1 END
    ) as `Frequency in C`
FROM tb
CROSS JOIN numbers v
GROUP BY Val

View working demo on DB Fiddle

Let me know if this works for you.

CodePudding user response：

Here's another approach using window functions

with f as (
    select *,
    Count(A) over(partition by A)Fa,
    Count(B) over(partition by B)Fb,
    Count(C) over(partition by C)Fc
    from t
),v as (
    select a Val from t
    union
    select b from t
    union
    select c from t
)

select distinct v.Val, Coalesce(fa.fa,0) FrequencyA, Coalesce(fb.fb,0)FrequencyB, coalesce(fc.fc,0) FrequencyC
from v
left join f fa on fa.A=v.val
left join f fb on fb.B=v.val
left join f fc on fc.C=v.val
order by val