CODEWITHSUNDEEP
sqlduplicates
Martina
Martina

Reputation: 811

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

Upvotes: 0

Views: 882

Answers (1)

Mattia Caputo
Mattia Caputo

Reputation: 969

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

Upvotes: 5

Related Questions