SUM, Case, and Subquery?

Transact-SQL
1

Hi,

It seems that I cannot combine these three together in my query. I would greatly appreciate if you could advise. Thank you very much.

SELECT arrestno,
sum(case when offs in (select offs from offense.dbo.offs where disp = 'A') THEN 1 ELSE 0 END) as arrcount
from arrest
group by arrestno
having count(arrestno) > 1

2

How about something like:

SELECT A.arrestno, 
 sum(case when O.offs IS NULL THEN 1 ELSE 0 END) as arrcount
from arrest A 
LEFT JOIN offense.dbo.offs O ON A.OFFS = O.OFFS AND O.disp = 'A'
group by A.arrestno
having count(A.arrestno) > 1
3

You can use an EXISTS clause, but you need to have some link between the arrest table and the offs table or the result will always by the same. Here's the base query as you've written it now:

SELECT arrestno,
sum(case when exists(select offs from offense.dbo.offs where disp = 'A') THEN 1 ELSE 0 END) as arrcount
from dbo.arrest
group by arrestno
having count(arrestno) > 1

But what you really need is some comparison between the two tables:

SELECT a.arrestno,
sum(case when exists(select 1 from offense.dbo.offs o where o.[some_column_name] = a.[some_column_name] and o.disp = 'A') THEN 1 ELSE 0 END) as arrcount
from dbo.arrest a
group by a.arrestno
having count(a.arrestno) > 1

4

Thanks Scott. Unfortunately, when I perform your query I get the same error as my original:

Msg 130, Level 15, State 1, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

5

Interesting.

You'll have to go with a LEFT OUTER JOIN then, although I think djj55 got the 1 and 0 backwards:

sum(case when O.offs IS NULL THEN 0 ELSE 1 END) as arrcount