CODEWITHSUNDEEP
mysqlsql
Art F
Art F

Reputation: 4202

Count number of distinct columns in SQL

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

Upvotes: 0

Views: 35

Answers (2)

Schwern
Schwern

Reputation: 164939

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

Upvotes: 2

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

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

Upvotes: 3

Related Questions