问题描述:

I have the following case statement given to me.

`SELECT schoolc,`

COUNT(CASE WHEN (subtestc) IN('ela','Math') AND tscrtypc ='A'THEN (ident) ELSE 0 END)AS [total Students],

COUNT(CASE WHEN (testscore)IN('A','P') AND subtestc IN('ela','Math') AND tscrtypc ='A'THEN (ident) ELSE 0 END)AS [students above]

FROM [dbo].[qw_star_testing_detail]

WHERE subtestc IN('ela','Math')

AND tscrtypc ='A'

GROUP BY schoolc

it is trying to do two separate calculations total students and student above but it is turning the same value for both. when I run this i get 1966 which is correct but when I apply it to my case i get 2362 . I has to be something simple.

`select COUNT(ident),schoolc`

FROM [dbo].[qw_star_testing_detail]

WHERE subtestc IN('ela','Math')

AND tscrtypc ='A'

AND testscore IN('A','P')

GROUP BY schoolc

ORDER BY schoolc

COUNT([column]) returns the number of rows in the result set that have a non-null value in the specified column. Try something like this:

```
SELECT schoolc,
SUM(CASE WHEN (subtestc) IN ('ela','Math') AND tscrtypc ='A'THEN 1 ELSE 0 END) AS [total Students],
SUM(CASE WHEN (testscore)IN ('A','P') AND subtestc IN ('ela','Math') AND tscrtypc ='A'THEN 1 ELSE 0 END) AS [students above]
FROM [dbo].[qw_star_testing_detail]
WHERE subtestc IN('ela','Math')
AND tscrtypc ='A'
GROUP BY schoolc
```

Remove the `ELSE 0`

portion of your `CASE`

Statements, then the records not matching the `CASE`

criteria will return `NULL`

which is excluded in aggregate functions.

```
SELECT schoolc
,COUNT(CASE WHEN (subtestc) IN('ela','Math') AND tscrtypc ='A'THEN (ident) END)AS [total Students]
,COUNT(CASE WHEN (testscore)IN('A','P') AND subtestc IN('ela','Math') AND tscrtypc ='A'THEN (ident) END)AS [students above]
FROM [dbo].[qw_star_testing_detail]
WHERE subtestc IN('ela','Math')
AND tscrtypc ='A'
GROUP BY schoolc
```