Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialIncorrect syntax near the keyword 'as'.

```
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
```

```
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
```

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

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

did you try that?

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
```

Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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...