Special grouped select in a SQL table

I have a table T with column A,B,C,...
The Primary Key is A,B,C
I need a select to list all A and the number of distinct B they have
So if the table is as follows :
A1  B1  C1
A1  B1  C2
A1  B2  C3
A1  B2  C4
A1  B2  C5
A2  B1  C1
A3  B1  C2

The result of the select is
A1,2
A1,1
A3,1
LeTayAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT colA, count(distinct colb)
FROM dbo.tablename
GROUP BY colA
0
 
esolveCommented:
SELECT tbl.A, COUNT(tbl.B) FROM
(
SELECT DISTINCT t.A,t.B FROM T
INNER JOIN T as T2 ON t.A = t2.A AND t.B = t2.B AND t.C = t2.C
)  tbl GROUP BY tbl.A

Haha ok Scott you win. I did not see the count so had to adjust mine. His answer is correct and easier
0
 
LeTayAuthor Commented:
Indeed, first select is beautiful !
0
All Courses

From novice to tech pro — start learning today.