case statement and aggregate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

case statement and aggregate

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-10-07 : 03:33:17

here is my query,


select 

'pmi' = 
case 
	when severity ='2. Major' then count(distinct shortid)*15
	when severity ='3. Minor' then count(distinct shortid)*5
	when severity ='1. Showstopper' then count(distinct shortid)*25
end
from DD where PP like 'XYZ%' and state in('Assigned') group by severity

i am getting result like this

-----
pmi
-----
165
40
225
-----

but i need the sum of it, 430. how can i do that

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2009-10-07 : 04:00:51

[code]
select sum(case severity
when '2. Major' then 15
when '3. Minor' then 5
when '1. Showstopper' then 25 end) as pmi
from defectall
where PrimaryPV like 'Dogan_SDPVR%'
and state in = 'Assigned'
[/code]

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-10-07 : 04:23:48

if i run this query


select 
sum(case 
	when severity ='2. Major' then count(distinct shortid)*15
	when severity ='3. Minor' then count(distinct shortid)*5
	when severity ='1. Showstopper' then count(distinct shortid)*25
end) as 'pmi'  
from DD where PP like 'XYZ %' and state in('Assigned') group by severity

i am getting this error


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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-07 : 04:25:30

put like this

select sum(pmi) from
(select 'pmi' =
case
when severity ='2. Major' then count(distinct shortid)*15
when severity ='3. Minor' then count(distinct shortid)*5
when severity ='1. Showstopper' then count(distinct shortid)*25
end
from defectall where PrimaryPV like 'Dogan_SDPVR%' and state in('Assigned') group by severity) a

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-10-07 : 04:39:55

I TRIED THIS JUST BEFORE YOU REPLY,,,, WORKED,

ANYWAY THANKS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources