CODEWITHSUNDEEP
sql
Jeff
Jeff

Reputation: 175

Finding unique combinations and their numbers using SQL

Stuck trying to write the SQL to find a list of unique combinations and their respective numbers such as given a table (mytable), column 1 (col1) and column 2 (col2)

mytable:

Col1  Col2
A      A1
B      A1
A      A1
B      B1
A      A2
B      B1

sql results

Col1  Col2  Number
A     A1     2
A     A2     1
B     A1     1
B     B1     2

I can get one part or the other but not both.

Upvotes: 0

Views: 290

Answers (2)

Daniele Cappuccio
Daniele Cappuccio

Reputation: 2202

SELECT DISTINCT Col1, Col2, count(*)
FROM mytable
GROUP BY Col1, Col2

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

Whats wrong with this?

Select col1, col2, count(*) from yourtable group by col1, col2

Upvotes: 2

Related Questions