Solved

# SQL Server Query sum case

Posted on 2014-12-10

I have a problem with a particular query that I am working on. The following two queries work fine:

SELECT

sum(case when ((r3000+r4000+r6000) / 3) < '26' then 1 else 0 end) as 'NORMAL',

sum(case when ((r3000+r4000+r6000) / 3) between '26' and '40' then 1 else 0 end) as 'MILD',

sum(case when ((r3000+r4000+r6000) / 3) between '41' and '55' then 1 else 0 end) as 'MODERATE',

sum(case when ((r3000+r4000+r6000) / 3) between '56' and '70' then 1 else 0 end) as 'MODERATELY-SEVERE',

sum(case when ((r3000+r4000+r6000) / 3) between '71' and '90' then 1 else 0 end) as 'SEVERE',

sum(case when ((r3000+r4000+r6000) / 3) > '90' then 1 else 0 end) as 'PROFOUND'

FROM Audio

SELECT

sum(case when ((l3000+l4000+l6000) / 3) < '26' then 1 else 0 end) as 'NORMAL',

sum(case when ((l3000+l4000+l6000) / 3) between '26' and '40' then 1 else 0 end) as 'MILD',

sum(case when ((l3000+l4000+l6000) / 3) between '41' and '55' then 1 else 0 end) as 'MODERATE',

sum(case when ((l3000+l4000+l6000) / 3) between '56' and '70' then 1 else 0 end) as 'MODERATELY-SEVERE',

sum(case when ((l3000+l4000+l6000) / 3) between '71' and '90' then 1 else 0 end) as 'SEVERE',

sum(case when ((l3000+l4000+l6000) / 3) > '90' then 1 else 0 end) as 'PROFOUND'

FROM Audio

But, I want to combine these two into one, with a condition, so that the sums are performed on the larger of r3000+r4000+r6000 or l3000+l4000+l6000

I cant seem to fine the right query to do this. Below is what I want, but I am getting an error..

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'as'.

SELECT

case when r3000+r4000+r6000 >= l3000+l4000+l6000 then

sum(case when ((r3000+r4000+r6000) / 3) < '26' then 1 else 0 end) as 'NORMAL',

sum(case when ((r3000+r4000+r6000) / 3) between '26' and '40' then 1 else 0 end) as 'MILD',

sum(case when ((r3000+r4000+r6000) / 3) between '41' and '55' then 1 else 0 end) as 'MODERATE',

sum(case when ((r3000+r4000+r6000) / 3) between '56' and '70' then 1 else 0 end) as 'MODERATELY-SEVERE',

sum(case when ((r3000+r4000+r6000) / 3) between '71' and '90' then 1 else 0 end) as 'SEVERE',

sum(case when ((r3000+r4000+r6000) / 3) > '90' then 1 else 0 end) as 'PROFOUND'

else

sum(case when ((l3000+l4000+l6000) / 3) < '26' then 1 else 0 end) as 'NORMAL',

sum(case when ((l3000+l4000+l6000) / 3) between '26' and '40' then 1 else 0 end) as 'MILD',

sum(case when ((l3000+l4000+l6000) / 3) between '41' and '55' then 1 else 0 end) as 'MODERATE',

sum(case when ((l3000+l4000+l6000) / 3) between '56' and '70' then 1 else 0 end) as 'MODERATELY-SEVERE',

sum(case when ((l3000+l4000+l6000) / 3) between '71' and '90' then 1 else 0 end) as 'SEVERE',

sum(case when ((l3000+l4000+l6000) / 3) > '90' then 1 else 0 end) as 'PROFOUND'

end

FROM Audio