Distinct on multiple columns in MySQL

Question

I wish to find out the count of distinct rows in a MySQL DB.

id | val1 | val2 | val3

1  |  1   |  1   |  1
2  |  1   |  1   |  1
3  |  2   |  2   |  2
4  |  2   |  2   |  2

On the table above the query would return

val1 | val2 | val3 | count
1    |   1  |  1   |   2 
2    |   2  |  2   |   2

Does anyone know a reasonably efficient way of achieving this in MySQL.

Himanshu · Accepted Answer · 2012-08-24 05:34:22Z

5

You need to use GROUP BY clause for this:

SELECT val1, val2, val3, count(*) AS count
FROM mytable GROUP BY val1, val2, val3

See this fiddle

answered Aug 24, 2012 at 4:46

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

@marabutt and Spechal it is really nice tool.