how to do this where is error - 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

SQL Server Development (2000)

how to do this where is error

Author

Topic

creatives
Starting Member

1 Post

Posted - 2012-11-19 : 03:56:35

select a.memberid,sum(a.MonthlyBonus)as MonthlyBonus,sum(a.FestivalBonus)as FestivalBonus,(a.OfficerBonus)as OfficerBonus from a
(
select memberid,sum(amount)as MonthlyBonus,0 as FestivalBonus,0 as OfficerBonus from tblmember_comission where tag='MB' group by memberid,amount
union all
select memberid,0 as MonthlyBonus,sum(amount)as FestivalBonus,0 as OfficerBonus from tblmember_comission where tag='FB' group by memberid
union all
select memberid,0 as MonthlyBonus,0 as FestivalBonus,sum(amount)as OfficerBonus from tblmember_comission where tag='OB' group by memberid) a

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 06:40:08

Remove the "a" at the end of the first line - see in red below:

select a.memberid,sum(a.MonthlyBonus)as MonthlyBonus,sum(a.FestivalBonus)as FestivalBonus,(a.OfficerBonus)as OfficerBonus from a
(
...

You can shorten the code as shown below, which would avoid the UNION ALL and the subquery, and so is likely to be more efficient as well

SELECT
	memberid,
	SUM(CASE WHEN tag = 'MB' THEN amount ELSE 0 END) AS MonthlyBonus,
	SUM(CASE WHEN tag = 'FB' THEN amount ELSE 0 END) AS FestivalBonus,
	SUM(CASE WHEN tag = 'OB' THEN amount ELSE 0 END) AS OfficerBonus
FROM
	tblmember_comission
GROUP BY
	memberid;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 03:00:37

or even this if sql 2005 and above



SELECT member_id,
[MB] AS MonthlyBonus,
[FB] AS FestivalBonus,
[OB] AS OfficerBonus
FROM (SELECT member_id,tag,amount FROM tblmember_comission)t
PIVOT(SUM(amount) FOR tag IN ([MB],[FB],[OB]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources