MartinaLabMath MartinaLabMath - 6 months ago 11
SQL Question

Count the occurrences of duplicate values among columns of a table

I've a table where each column contains integers and some values are duplicates.
Here the example:

| ColumnA | Column B | Column C |
| 2 | 3 | 1 |
| 1 | 1 | 3 |
| 2 | 1 | 3 |


How can I do a SQL query in order to count the occurrences of each integer?
I want to obtain something like that:
the count for 2 is 2, the count for 1 is 4, the count for 3 is 3

Answer

try this:

SELECT Col, COUNT(*) AS TOT
FROM (
    SELECT ColumnA AS Col FROM table
    UNION ALL
    SELECT ColumnB FROM table
    UNION ALL
    SELECT ColumnC FROM table
) AS A
GROUP BY Col
Comments