SQL COUNT(*) Function

[Genom]

I am tring to make a query but I am a beginner in SQL commands. Example is following:

There is the table which is my database and Query will look like that. I have added everything to picture.

I am using MS SQL 2005
(Sorry about my english)

Note: The result of the query shall look like this:
ID2___Count_____Count of ID2 with Option=1_____Count of ID2 withOption=2
1_____5_________3___________________________2
2_____4_________0___________________________0
3_____6_________1___________________________2
4_____4_________2___________________________1


Thanks for every kind of help

[amrita]

Check this crosstab query .You can modify it according to your need

SELECT ID2,
count(case [Option] when 1 then 1 else null end) as [ID2-Option1],
count(case [Option] when 2 then 1 else null end) as [ID2-Option2]
FROM TEST where Option in(1,2)
GROUP BY ID2

[Genom]

does it work in Access too?

[amrita]

For creating crosstab query in Access Check for TRANSFORM syntax.
Check this

TRANSFORM Count(TEST.ID2) AS CountOfID2
SELECT TEST.ID2
FROM TEST
GROUP BY TEST.ID2
PIVOT "CountOfID2-" & TEST.Id2 In ("Option-1","Option-2","Option-3");

[Genom]

Thanks for your help...
I have seen that the columnname Option is being put in "[]" What does it mean? And how would the code if I would use another name like "IDOption"?
And can you explain this code please:

SELECT ID2,
count(case [Option] when 1 then 1 else null end) as [ID2-Option1],
count(case [Option] when 2 then 1 else null end) as [ID2-Option2]
FROM TEST where Option in(1,2)
GROUP BY ID2

[amrita]

Read about CrossTab Queries in SQL