CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
kravits88
kravits88

Reputation: 13049

TSQL Count Case When In

I am having trouble with some logic here. Trying to get a count of rows where S.ID's not in my subquery.

COUNT(CASE WHEN S.ID IN
(SELECT DISTINCT S.ID FROM...) 
THEN 1 ELSE 0 END)

I am recieving the error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How to fix this or an alternative?

Upvotes: 0

Views: 533

Answers (3)

potashin
potashin

Reputation: 44581

Using EXISTS :

SELECT COUNT(t.*) FROM table1 t WHERE NOT EXISTS (SELECT * FROM table2 WHERE ID = t.ID)

Upvotes: 2

user353gre3
user353gre3

Reputation: 2755

Use following construct with CTE. 

  with cte as 
  (
  select 
    case
     when S.ID in (SELECT DISTINCT S.ID FROM LookupTable) then 1
     else 0
    end
    as SID
  from MyTable)
  select count(SID) as SIDCOUNT from cte;

Upvotes: 1

SqlACID
SqlACID

Reputation: 4014

Maybe something like this?

SELECT COUNT(*) FROM .... WHERE ID NOT IN (SELECT DISTINCT ID FROM ...)

Upvotes: 5

Related Questions