Count number of distinct columns in SQL

Question

Let's say I have the following query:

SELECT DISTINCT(Value1,value2,value3) From table1

and get something like

        #   value1  value2  value3
--------------------------------------
        1.  result1 result1 result2
        2.  result1 result2 result2
        3.  result5 result6 result7

How would I add an extra column that tells me how many times that unique combination of values appears, such as (last column is how many times it occurs):

 #    value1  value2  value3      #occurred
--------------------------------------
 1.  result1 result1 result2      15
 2.  result1 result2 result2      25
 3.  result5 result6 result7      35

Note that distinct is not a function. Instead, distinct alters the behavior of select. Think of it as select distinct, as opposed to select all, rather than choosing columns to select. You shouldn't put parens around it, that's some non-standard MySQL thing. — Schwern
– Schwern, Commented Jan 31, 2017 at 23:02
DISTINCT is a keyword that applies to the entire list of expressions, it's not a function. The parens aren't necessary. Extraneous parens aren't invalid, but there's no reason to include them. As vkp notes, remove the DISTINCT keyword, add a GROUP BY clause, and add a COUNT() aggregate to the SELECT list. — spencer7593
– spencer7593, Commented Jan 31, 2017 at 23:03

David דודו Markovitz · Accepted Answer · 2017-01-31 22:55:04Z

3

SELECT    Value1,value2,value3,count(*)
From      table1
group by  Value1,value2,value3

answered Jan 31, 2017 at 22:55

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

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

Comments

Schwern · Accepted Answer · 2017-01-31 22:57:45Z

Use a group by instead of distinct. Then you can use count.

select value1, value2, value3, count(*) as occurred
from table1
group by value1, value2, value3;

A demonstration in SQLite.

sqlite> select * from table1 order by value1, value2, value3;
value1      value2      value3    
----------  ----------  ----------
1           1           2         
1           1           2         
1           2           2         
1           2           2         
1           2           2         
5           6           7         

sqlite> select value1, value2, value3, count(*) as '#occurred' from table1 group by value1, value2, value3;
value1      value2      value3      #occurred 
----------  ----------  ----------  ----------
1           1           2           2         
1           2           2           3         
5           6           7           1

Collectives™ on Stack Overflow

Count number of distinct columns in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related