Count statement

[codo]

I have Dept table like this:

select * from Dept

ID Class
--------------
01 A
01 A
01 B
02 A
02 B
02 B
02 B
03 C


I want to create a view so the result goes like this :

ID Count_A Count_B Count_C
---------------------------------------
01 2 1 0
02 1 3 0
03 0 0 1

I don't know how to type the SQL commands for it, plz help me!

 

[bborissov]

SELECT Id,
       SUM(CASE WHEN Class ='A' THEN 1 ELSE 0 END) AS Count_A,
       SUM(CASE WHEN Class ='B' THEN 1 ELSE 0 END) AS Count_B,
       SUM(CASE WHEN Class ='C' THEN 1 ELSE 0 END) AS Count_C,
FROM Dept

Borislav Borissov

 

[Geoka]

What about if the columns are indefine!!!!

 

[bborissov]

What do you mean?


Oops, I just saw I have typo, remove comma after Count_C

Borislav Borissov

 

[Geoka]

Not that one bborissov, suppose we cannot predict the number of Classes in advance (indefinite), that may vary. in that case how we will relay on this query.

If the classes are predictable in advance then your query will work fine Got my point?!!

 

[vbkris]

then u may have to do some extra work like using cursors.

--Take Distinct Classes
select distinct Class from Dept

declare @sql varchar(8000)
set @sql='SELECT Id,'
--Open cursor and go through the loop

set @sql=@sql+'SUM(CASE WHEN Class ='''+ @TheVal +''' THEN 1 ELSE 0 END) AS Count_'''+ @TheVal +''','

--End Loop

set @sql=@sql+' From Dept'


Known is handfull, Unknown is worldfull

 

[r937]

import the data into microsoft access and do a simple crosstab query

r937.com | rudy.ca

 

[bborissov]

O, I see what you mean, Geoka. If you din't know how many classes you have in table then, I think, Vbkris' suggestion should work.

Borislav Borissov

 

[codo]

Thanks guys for all the suggestions