count distinct values multiple col

Question

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

fancyPants · Accepted Answer · 2016-05-20 13:01:55Z

1

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);

edited May 20, 2016 at 13:01

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

answered May 20, 2016 at 12:24

mimzee

991 silver badge8 bronze badges

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

2 Comments

Sliva Over a year ago

if i am use union, i am can't see what key was count. This values and keys - options for filter and i am need count elements in result for each option

fancyPants Over a year ago

@Gelid There, I fixed it. @mimzee Use union all when results will be distinct anyway or you don't want the implicit distinct of union to filter results. For performance reasons...

Collectives™ on Stack Overflow

count distinct values multiple col

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related