GROUP BY 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 2008 Forums

Transact-SQL (2008)

GROUP BY Error

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-11-08 : 03:45:44

Hi

I have this code:

SELECT

pwh.IFA_Company_Name as IFA_Company_Name,
t3.Client_Full_Name as Client_Full_Name,
t3.SecondInvestor as SecondInvestor,
IB.POLICY_ID AS Policy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value

UNION

SELECT

pwh.IFA_Company_Name as IFA_Company_Name,
t3.Client_Full_Name as Client_Full_Name,
t3.SecondInvestor as SecondInvestor,
PIH.POLICY_ID AS Policy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value

and after my joins I have:

GROUP BY
pwh.IFA_Company_Name
, t3.Client_Full_Name
, t3.SecondInvestor
, IB.POLICY_ID
, PIH.POLICY_ID
, FS.VALUE
, FP.FUND_PRICE

ORDER BY IB.POLICY_ID

and I get this error:

Msg 8120, Level 16, State 1, Line 45
Column 'DWH.dbo.dim_policywhoswho.IFA_Company_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 104, Level 16, State 1, Line 45
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.Please help.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-08 : 04:15:42

While using SET operators you must have to specify GROUP BY clauses separately for each SELECT statement....

SELECT

pwh.IFA_Company_Name as IFA_Company_Name,
t3.Client_Full_Name as Client_Full_Name,
t3.SecondInvestor as SecondInvestor,
IB.POLICY_ID AS Policy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value

FROM ...............

GROUP BY
pwh.IFA_Company_Name
, t3.Client_Full_Name
, t3.SecondInvestor
, IB.POLICY_ID
, PIH.POLICY_ID
, FS.VALUE
, FP.FUND_PRICE

UNION

SELECT
pwh.IFA_Company_Name as IFA_Company_Name,
t3.Client_Full_Name as Client_Full_Name,
t3.SecondInvestor as SecondInvestor,
PIH.POLICY_ID AS Policy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value

FROM ...............

GROUP BY
pwh.IFA_Company_Name
, t3.Client_Full_Name
, t3.SecondInvestor
, IB.POLICY_ID
, PIH.POLICY_ID
, FS.VALUE
, FP.FUND_PRICE

ORDER BY IB.POLICY_ID

--
Chandu