SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 GROUP BY Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 11/08/2012 :  03:45:44  Show Profile  Reply with Quote
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.

Edited by - stahorse on 11/08/2012 03:57:12

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/08/2012 :  04:15:42  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 11/08/2012 :  04:37:14  Show Profile  Reply with Quote
Thank you
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/08/2012 :  04:52:19  Show Profile  Reply with Quote
quote:
Originally posted by stahorse

Thank you


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000