问题描述:

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
相关阅读:
Top