SQL Server Count() and Distinct in Same Query

I have an employee table with 4 columns (id, type, class, and name). I want to count the number of employees in the table for each unique combination of type and class. So if the table looks like:

ID           Type          Class         Name
123           I                  A            Smith
124           I                  A            Jones
129           I                  C            Franklin
125          I I                 A            Jenkins
126          I I                 B            Smith

I want my result set to look like:

Type       Class       Count
   I               A               2
   I               C               1
  I I              A               1
   I               B               1

I've tried the following but SQL Server doesn't like it

select count(*)
from (
select distinct type, class from employee)

Any ideas?

Thanks in advance for your help
cbridgmanAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
SELECT Type, Class, Count(1) qty
FROM employee
GROUP BY Type, Class

Open in new window

0
 
FarWestCommented:
did you try this?

select count(*),type,class from employee
group by type,class
0
 
cbridgmanAuthor Commented:
Thanks for the feedback. I searched all over the place for the answer to this and couldn't find anything simple like this. Works like a charm.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.