difficult query (1 Viewer)

[aqif]

Hi

lets look at my raw data

ID Score
1 2
1 3
1 2
1 1

*There could only be score from 1 to 3

From this data I want following result

ID CountOfScore1 CountOfScore2 CountOfScore3
1 1 2 1

One way of doing this is to make 3 subqueries within the main query like

Select ID,Count(ID) As CountOfScore1,(Select Count(ID) From TblTest As T Where T.ID=TblTest.ID And Score=2) As CountOfScore2 From TblTest Where Score=1;

This type of query really becomes slow if the calculation is complex........is there any alternative approach to the matter?

 

[Jon K]

Try this query:-

SELECT ID, Sum(iif(Score=1,1,0)) as CountOfScore1, Sum(iif(Score=2,1,0)) as CountOfScore2, Sum(iif(Score=3,1,0)) as CountOfScore3
FROM tblTest
GROUP BY ID