asked on

SQL Server Query sum case

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

Guy Hengel [angelIII / a3]

>< '26'

must be
< 26

otherwise you are doing a implicit data type conversion ...
the quotes is ONLY for text values, not for numerical values

as 'NORMAL',

must be either
as [NORMAL],
as NORMAL,
as "NORMAL",

again, the single quotes are for string values
the double quotes or [ ] is for names (identifiers), and they are optional unless you have special characters/reserved keywords etc...

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dpinchot

ASKER

Guy, Thanks for the assistance on the syntax issues. I modifies the query, but I am getting the same error.

Incorrect syntax near the keyword 'as'.

HainKurt

try this

with x as (
select a.*, case when r3000+r4000+r6000 >= l3000+l4000+l6000 then r3000+r4000+r6000/3 else l3000+l4000+l6000/3 end as average
from audio a
)
select *,
case 
when average between 0 and 25 then 'NORMAL'
when average between 26 and 40 then 'MODERATE'
...
end as Status
from x

Open in new window

HainKurt

or if you just need counts

with x as (
select case when r3000+r4000+r6000 >= l3000+l4000+l6000 then r3000+r4000+r6000/3 else l3000+l4000+l6000/3 end as average
from audio a
)
select 
sum(case when average between 0 and 25 then 1 else 0 end ) as 'NORMAL',
sum(case when average between 26 and 40 then 1 else 0 end) as 'MODERATE',
...
from x

Open in new window

dpinchot

ASKER

Guy,

I made a slight modification to your solution, and it ran just fine. t needed an extra end before the last )

SELECT
sum(case when r3000+r4000+r6000 >= l3000+l4000+l6000 then
case when ((r3000+r4000+r6000) / 3) < 26 then 1 else 0 end
else
case when ((l3000+l4000+l6000) / 3) < 26 then 1 else 0 end
end ) as NORMAL
FROM Audio

dpinchot

ASKER

The final solution with Guy's code looks like this, and it works great. Thanks for the help on this.

SELECT
sum(case when r3000+r4000+r6000 >= l3000+l4000+l6000 then
case when ((r3000+r4000+r6000) / 3) < 26 then 1 else 0 end
else
                   case when ((l3000+l4000+l6000) / 3) < 26 then 1 else 0 end
end ) as [NORMAL],
sum(case when r3000+r4000+r6000 >= l3000+l4000+l6000 then
case when ((l3000+l4000+l6000) / 3) between 26 and 40 then 1 else 0 end
else
                   case when ((l3000+l4000+l6000) / 3) between 26 and 40 then 1 else 0 end
end ) as [MILD],
            sum(case when r3000+r4000+r6000 >= l3000+l4000+l6000 then
case when ((l3000+l4000+l6000) / 3) between 41 and 55 then 1 else 0 end
else
                   case when ((l3000+l4000+l6000) / 3) between 41 and 55 then 1 else 0 end
end ) as [MODERATE],
            sum(case when r3000+r4000+r6000 >= l3000+l4000+l6000 then
case when ((l3000+l4000+l6000) / 3) between 56 and 70 then 1 else 0 end
else
                   case when ((l3000+l4000+l6000) / 3) between 56 and 70 then 1 else 0 end
end ) as [MODERATELY-SEVERE],
            sum(case when r3000+r4000+r6000 >= l3000+l4000+l6000 then
case when ((l3000+l4000+l6000) / 3) between 71 and 90 then 1 else 0 end
else
                   case when ((l3000+l4000+l6000) / 3) between 71 and 90 then 1 else 0 end
end ) as [SEVERE],
            sum(case when r3000+r4000+r6000 >= l3000+l4000+l6000 then
case when ((l3000+l4000+l6000) / 3) > 90 then 1 else 0 end
else
                   case when ((l3000+l4000+l6000) / 3) > 90 then 1 else 0 end
end ) as [PROFOUND]
FROM Audio

HainKurt

the one I posted above (ID: 40491895) should do the same and it is very simple & readable...

did you try that?

dpinchot

ASKER

I am boarding a flight, but I will try it out when I can get back to my laptop.

PortletPaul

I agree with HainKurt, I think the use of case within case leads to overly complex code.

Just as a tip however you don't have to use WITH AS (...) instead you can use CROSS APPLY like this:

SELECT
      SUM(CASE
            WHEN ca.average BETWEEN 0 AND 25 THEN 1
            ELSE 0
      END) AS 'NORMAL'
    , SUM(CASE
            WHEN ca.average BETWEEN 26 AND 40 THEN 1
            ELSE 0
      END) AS 'MODERATE'--,
FROM audio
      CROSS APPLY (
            SELECT
                  CASE
                        WHEN r3000 + r4000 + r6000 >= l3000 + l4000 + l6000 THEN r3000 + r4000 + r6000 / 3
                        ELSE l3000 + l4000 + l6000 / 3
                  END AS average
      ) AS ca

Open in new window