Adrian Adyms Adrian Adyms - 2 months ago 10
SQL Question

SQL count occurrences rows

I have a table like this:


c1|c2
a| 2
b| 1
a| 1
b| 3
a| 2


how count this to get result like this


col1| col2| number of occurrences
a| 1| 1
a| 2| 2
b| 3| 1


And after get max value like this:

col1col2

a|2

b|1

Answer

Count 1 is your friend, count * leads to a longer load time.

SELECT C1, C2, COUNT(1) AS numberofoccurrences
FROM TABLE 
GROUP BY C1, C2