CODEWITHSUNDEEP
sql
August Ragnesjö
August Ragnesjö

Reputation: 1

SQL: How to check for each unique value in one column how many combinations of another column can be found

Working in SSMS. Based on a value in one column I want to count the number of different occurrences in another column. How would I go about to do that?

Basically for each unique value in Column 1, how many combinations of Column 2 can be found?

So if table looks like this:

Column 1 Column 2
A 1
A 1
A 1
B 1
B 2
B 3
C 2
C 2
C 3

Desired output would then be:

A = 1

B = 3

C = 2

Upvotes: 0

Views: 34

Answers (2)

Nguyễn Văn Quyền
Nguyễn Văn Quyền

Reputation: 350

Use COUNT(DISTINCT) for this case with GROUP BY:

SELECT Col1, COUNT(DISTINCT Col2)
FROM YourTable
GROUP BY Col1

dbfiddle here

Upvotes: 2

Sergey
Sergey

Reputation: 5225

WITH CTE(Column1,Column2) AS
(

  SELECT 'A',   1 UNION ALL
  SELECT 'A' ,  1 UNION ALL
  SELECT 'A' ,  1 UNION ALL
  SELECT 'B' ,  1 UNION ALL
  SELECT 'B' ,  2 UNION ALL
  SELECT 'B' ,  3 UNION ALL
  SELECT 'C' ,  2 UNION ALL
  SELECT 'C' ,  2 UNION ALL
  SELECT 'C' ,  3
)
SELECT C.COLUMN1,COUNT(DISTINCT C.COLUMN2)CNTT
 FROM CTE AS C
 GROUP BY C.Column1

CTE is an example you provided. Please replace reference to it with reference to your table

Upvotes: 0

Related Questions