CODEWITHSUNDEEP
mysql
Stu Ayton
Stu Ayton

Reputation: 489

Counting distinct values across columns

I have data as such:

-----------------------------------------------------
|id |  col1 |  col2 |  col3 |  col4 |  col5 |  col6 |
-----------------------------------------------------
| 1 |   12  |    0  |   10  |   12  |    0  |   11  |
| 2 |   12  |    0  |    0  |   10  |    0  |   11  |
| 3 |   12  |   14  |    0  |    0  |    0  |   11  |
| 4 |   12  |    0  |    0  |   11  |   14  |   11  |
| 5 |   12  |    0  |   14  |   10  |    0  |   11  |
| 6 |   12  |   10  |    0  |    0  |    0  |   11  |
-----------------------------------------------------

I need to return the count of all distinct values across the 6 columns as such:

----------------
| Qty | myValue|
----------------
|  4  |   10   |
|  7  |   11   |
|  7  |   12   |
|  3  |   14   |
----------------

Is this possible in one query? I can do this on one column like this:

SELECT count(*) AS Qty, col1 AS myValue FROM myTable GROUP BY col1

but unsure how to include the other 5 columns within that statement

Upvotes: 1

Views: 44

Answers (2)

Yosra Hamza
Yosra Hamza

Reputation: 549

Using UNION ALL you can get each column separately and group by the count at the end

SELECT myValue, COUNT(*) as total
FROM 
  (
    (SELECT col1 as myValue FROM a)
    UNION ALL
    (SELECT col2 as myValue FROM a)
    UNION ALL
    (SELECT col3 as myValue FROM a)
    UNION ALL
    (SELECT col4 as myValue FROM a)
    UNION ALL
    (SELECT col5 as myValue FROM a)
    UNION ALL
    (SELECT col6 as myValue FROM a)
  ) T
GROUP BY myValue
HAVING myValue > 0;

see SQL fiiddle demo

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You could use union for your current structure, but i guess you might need a better structure for your data

select col,count(*) as qty
from(
    select col1 as col from table
    union all
    select col2 as col from table
    union all
    select col3 as col from table
    union all
    select col4 as col from table
    union all
    select col5 as col from table
    union all
    select col6 as col from table
) t
group by col

Upvotes: 0

Related Questions