Solved

SQL Server Query sum case

Posted on 2014-12-10
190 Views
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
0
Question by:dpinchot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 143

Expert Comment

ID: 40491849
>< '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...
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 40491865
also, you cannot build 2 column lists with a included CASE construct like this:::

so you must restructure like 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
)
as [NORMAL]

FROM Audio
0

Author Comment

ID: 40491885
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'.
0

LVL 55

Expert Comment

ID: 40491887
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
``````
0

LVL 55

Expert Comment

ID: 40491895
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
``````
0

Author Comment

ID: 40491905
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
0

Author Comment

ID: 40491936
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
0

LVL 55

Expert Comment

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

did you try that?
0

Author Comment

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

LVL 49

Expert Comment

ID: 40493272
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
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiâ€¦
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability â€¦
If youâ€™ve ever visited a web page and noticed a cool font that you really liked the look of, but couldnâ€™t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yoâ€¦
Suggested Courses
Course of the Month2 days, 15 hours left to enroll