CODEWITHSUNDEEP
mysqlcountdistinct
Sliva
Sliva

Reputation: 63

count distinct values multiple col

I have this table (model - not table in SQL, all keys are joined to a real table but I need to count keys which exist in this model.)

key1   key2  key 3
v1     d1    t1
v2     d2    t1
v3     d1    t4
v1     d3    t8
v1     d2    t4

The result needs to return 

key1 v1 3
key1 v2 1
key1 v3 1
key2 d1 2

fiddle for test http://sqlfiddle.com/#!9/c102f

I can't select all the keys one by one, because the number of keys = 79 and rows num = 1 115 260

one by one select process takes 2 mins

Upvotes: 1

Views: 51

Answers (1)

mimzee
mimzee

Reputation: 99

You can't groupby separate columns in a single select statement as the first group would effect the 2nd etc.

There is no other way besides selecting them one by one, but if you just want a to get everything into one result you can use union

(select 'key1' AS column_origin, key1, count(key1) from TestTable group by key1)
union all
(select 'key2', key2, count(key2) from TestTable group by key2)
union all
(select 'key3', key3, count(key3) from TestTable group by key3);

Upvotes: 1

Related Questions