Adding a Where clause for one column in a Select

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

General SQL Server Forums

New to SQL Server Programming

Adding a Where clause for one column in a Select

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-11-03 : 13:16:26

Hello,
I have the following select statement which works fine:

select o.prefix as Prefix,o.organizationName as Organization, SUM(a.amount) as TotalSpent,
o.AwardBucketMoney as Allocation,
o.AwardBucketMoney-Sum(a.amount) as Remainder, SUM(a.amount)/o.AwardBucketMoney AS PercentSpent from AwardOrganizations o
inner join LU_CostCenters c on o.Prefix = SUBSTRING(c.OrgCode,1,2)
INNER JOIN Position R ON R.Pos_LU_CostCenters_ID=c.LU_CostCenters_ID
inner join Person p on p.Person_ID=R.Pos_Person_ID
inner join Awards a on a.AWA_Person_ID = p.Person_ID
where o.PartOfMedCtr = 1
group by o.OrganizationName, o.AwardBucketMoney,o.Prefix

I would like to return one more column, which is the SUM(a.amount) where a.AwardType = 4.

I'm not sure how to put a WHERE clause for just one of the columns, like this. What I tried (but gave an error) was:

select o.prefix as Prefix,o.organizationName as Organization, SUM(a.amount) as TotalSpent,

SUM(a.Amount) where a.AwardType=4 as TotalPerfSpent

o.AwardBucketMoney as Allocation,
o.AwardBucketMoney-Sum(a.amount) as Remainder, SUM(a.amount)/o.AwardBucketMoney AS PercentSpent from AwardOrganizations o
inner join LU_CostCenters c on o.Prefix = SUBSTRING(c.OrgCode,1,2)
INNER JOIN Position R ON R.Pos_LU_CostCenters_ID=c.LU_CostCenters_ID
inner join Person p on p.Person_ID=R.Pos_Person_ID
inner join Awards a on a.AWA_Person_ID = p.Person_ID
where o.PartOfMedCtr = 1
group by o.OrganizationName, o.AwardBucketMoney,o.Prefix

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-03 : 13:28:02

This????


    SELECT    o.prefix as Prefix
			, o.organizationName as Organization
			, SUM(a.amount) as TotalSpent 
			, o.AwardBucketMoney as Allocation
			, o.AwardBucketMoney-Sum(a.amount) as Remainder
			, SUM(a.amount)/o.AwardBucketMoney AS PercentSpent from AwardOrganizations o
INNER JOIN    LU_CostCenters c 
		ON	  o.Prefix = SUBSTRING(c.OrgCode,1,2)  -- should cause a scan
INNER JOIN	  PositiON R 
		ON	  R.Pos_LU_CostCenters_ID=c.LU_CostCenters_ID
INNER JOIN	  PersON p 
		ON	  p.Person_ID=R.Pos_Person_ID
INNER JOIN	  Awards a 
		ON	  a.AWA_Person_ID = p.Person_ID 
INNER JOIN	  ( AWA_Person_ID, SUM(amount) AS SUM_amount FROM Awards where AwardType = 4) AS XXX
		ON	  XXX.AWA_Person_ID = p.Person_ID 
	 WHERE	  o.PartOfMedCtr = 1
  GROUP BY	  o.OrganizationName, o.AwardBucketMoney,o.Prefix

you really should format your code better

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-11-03 : 13:50:00

The statement you suggested is giving Invalid Object Name on AWA_Person_ID. (4th line from bottom)
I'd try to fix it but I'm not sure what you're trying to do.
It also gives an error on the same line, on SUM(amount), saying "an expression of non-boolean type, where a condition is expected".
I'll format better in the future. Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 15:34:44

Try:
SUM(CASE WHEN a.AwardType=4 THEN a.Amount ELSE 0 END) AS TotalPerfSpent
in your SELECT list.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources