GROUP BY Error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » GROUP BY Error

GROUP BY Error

Rate Topic

Display Mode

Topic Options

Author

Message

hoseam

Posted Thursday, November 08, 2012 1:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:16 AM
Points: 36, Visits: 65

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.

Post #1382323

anthony.green

Posted Thursday, November 08, 2012 1:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

Please post the full query.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1382325

hoseam

Posted Thursday, November 08, 2012 2:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:16 AM
Points: 36, Visits: 65

declare @CompanyName Varchar (20)

set @CompanyName = '2-G9BF'

SELECT PolicyID, Client_Full_Name
INTO #Stage1
FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)
WHERE IFA_Company_ID = @CompanyName
AND Master_Client = 'Y'

SELECT PolicyID, Client_Full_Name
INTO #Stage2
FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)
WHERE IFA_Company_ID = @CompanyName
AND Master_Client = 'N'

-- get the maximum investment holding per policy into a temp table
SELECT IH.policy_id, MAX(IH.inv_holding) as [max_INV_Holding]
INTO #MaxInvestHoldingPerPolicy
FROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_Sold_Prod SP WITH (NOLOCK)
JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_HOLDING IH WITH (NOLOCK)
ON IH.policy_id = SP.policy_id AND IH.product_code = SP.product_code AND IH.instance = SP.instance
JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)
ON FPB.policy_id = IH.POLICY_ID AND FPB.product_code = IH.product_code AND FPB.instance = IH.instance
WHERE FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'
GROUP BY IH.policy_id

SELECT
t1.PolicyID
, t1.Client_Full_Name
, ISNULL (t2.Client_Full_Name,'') SecondInvestor
INTO #Stage3
FROM #Stage1 t1
LEFT JOIN #Stage2 t2
ON t1.PolicyId = t2.PolicyID

ORDER BY t1.PolicyID

DROP TABLE #Stage1
DROP TABLE #Stage2

-- Get the first fields

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 SELESTIA_BI_STAGING_SOURCE.dbo.PR_IH_BASIS IB WITH (NOLOCK)

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.pr_ih_fund_spd FS WITH (NOLOCK)
ON FS.POLICY_ID = IB.POLICY_ID
AND FS.INV_HOLDING = IB.INV_HOLDING

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.s_asset SA WITH (NOLOCK)
ON SA.ASSET_NUM = IB.POLICY_ID

LEFT Join SELESTIA_BI_STAGING_SOURCE.dbo.pr_inv_ptf IP WITH (NOLOCK)
ON IP.INV_PTF_NAME = FS.INV_PTF_NAME

LEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)
ON FP.INV_PTF_NAME = IP.INV_PTF_ALIAS
AND EFFECTIVE_DATE = (SELECT MAX (EFFECTIVE_DATE) FROM DWH..FACT_FUNDPRICES WITH (NOLOCK))

LEFT JOIN DWH.dbo.dim_policywhoswho PWH WITH (NOLOCK)
ON PWH.POLICYID = IB.POLICY_ID

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_SOLD_PROD SP WITH (NOLOCK)
ON SP.POLICY_ID = IB.POLICY_ID AND SP.PRODUCT_CODE = IB.PRODUCT_CODE AND SP.INSTANCE = IB.INSTANCE

LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_INV_Holding IH WITH (NOLOCK)
ON IH.POLICY_ID = IB.POLICY_ID AND IH.PRODUCT_CODE = IB.PRODUCT_CODE AND IH.INSTANCE = IB.INSTANCE AND IH.INV_HOLDING = IB.INV_HOLDING

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)
ON FPB.POLICY_ID = IH.POLICY_ID AND FPB.PRODUCT_CODE = IH.PRODUCT_CODE AND FPB.INSTANCE = IH.INSTANCE AND FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'

LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_SPLIT_FEES SF WITH (NOLOCK)
ON SF.POLICY_ID = IB.POLICY_ID
AND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPE
AND SF.IBU_COMM_CLASS = IB.IBU_COMM_CLASS

LEFT JOIN #MaxInvestHoldingPerPolicy MH WITH (NOLOCK)
ON MH.POLICY_ID = SP.POLICY_ID

-- get the ServicingContact (amended for Flare 450396)
LEFT JOIN (

SELECT a.INTEGRATION_ID , a.FST_NAME + ' ' + a.LAST_NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_CONTACT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_SER_CON_ID = b.ROW_ID
WHERE a.X_INVSTR_FLG = 'Y'

UNION

SELECT a.INTEGRATION_ID , a.NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_ORG_EXT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_PR_SER_PER_ID = b.ROW_ID
WHERE a.INVSTR_FLG = 'Y'
) UN

ON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULT

LEFT JOIN #Stage3 t3
ON pwh.PolicyID = t3.PolicyID

WHERE fs.fund_code = 'FV'
AND FS.Value >= 1
AND SA.owner_accnt_id = @CompanyName
AND pwH.Master_Client = 'Y'
AND pwH.status IN ('VERI','INNF')
AND SP.IFA_REMUNE_TYPE = 'COMMISSION'

---then get the second fields

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 SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_HOLDING PIH WITH (NOLOCK)

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_IH_FUND_SPD FS WITH (NOLOCK)
ON PIH.POLICY_ID = FS.POLICY_ID
AND PIH.INV_HOLDING = FS.INV_HOLDING

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.S_ASSET SA WITH (NOLOCK)
ON PIH.POLICY_ID = SA.ASSET_NUM

LEFT Join SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_PTF IP WITH (NOLOCK)
ON FS.INV_PTF_NAME = IP.INV_PTF_NAME

LEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)
ON IP.INV_PTF_ALIAS = FP.INV_PTF_NAME
AND EFFECTIVE_DATE = (SELECT MAX (EFFECTIVE_DATE) FROM DWH..FACT_FUNDPRICES WITH (NOLOCK))

LEFT JOIN DWH.dbo.DIM_POLICYWHOSWHO PWH WITH (NOLOCK)
ON PIH.POLICY_ID = PWH.POLICYID

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_SOLD_PROD SP WITH (NOLOCK)
ON PIH.POLICY_ID = SP.POLICY_ID
AND PIH.PRODUCT_CODE = SP.PRODUCT_CODE
AND PIH.INSTANCE = SP.INSTANCE

LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_IH_BASIS IB WITH (NOLOCK)
ON PIH.POLICY_ID = IB.POLICY_ID
AND PIH.INV_HOLDING = IB.INV_HOLDING
AND PIH.PRODUCT_CODE = IB.PRODUCT_CODE
AND PIH.INSTANCE = IB.INSTANCE

LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)
ON PIH.POLICY_ID = FPB.policy_id
AND PIH.product_code = FPB.product_code
AND PIH.instance = FPB.instance
AND FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'

LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_SPLIT_FEES SF WITH (NOLOCK)
ON PIH.POLICY_ID = SF.POLICY_ID
AND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPE
--AND SF.IBU_COMM_CLASS = PIH.IBU_COMM_CLASS

LEFT JOIN #MaxInvestHoldingPerPolicy MH WITH (NOLOCK)
ON MH.POLICY_ID = SP.POLICY_ID

-- get the ServicingContact (amended for Flare 450396)
LEFT JOIN (

SELECT a.INTEGRATION_ID , a.FST_NAME + ' ' + a.LAST_NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_CONTACT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_SER_CON_ID = b.ROW_ID
WHERE a.X_INVSTR_FLG = 'Y'

UNION

SELECT a.INTEGRATION_ID , a.NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_ORG_EXT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_PR_SER_PER_ID = b.ROW_ID
WHERE a.INVSTR_FLG = 'Y'
) UN

ON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULT

LEFT JOIN #Stage3 t3
ON pwh.PolicyID = t3.PolicyID

WHERE fs.fund_code = 'FV'
AND FS.Value >= 1
AND SA.owner_accnt_id = @CompanyName
AND pwH.Master_Client = 'Y'
AND pwH.status IN ('VERI','INNF')
AND SP.IFA_REMUNE_TYPE = 'FEES'

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

DROP TABLE #Stage3
DROP TABLE #MaxInvestHoldingPerPolicy

Post #1382331

anthony.green

Posted Thursday, November 08, 2012 2:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

The first thing that stands out is why do you have all the NOLOCK hints? You are aware of the problems that this hint causes?

Second you need to treat each SELECT in a UNION as its own grouping set

So you would group up the first select, union, group the second select

SELECT
    Col1,
    Col2,
FROM
    Tab1
GROUP BY
    Col1
UNION
SELECT
    Col1,
    Col2
FROM
    Tab2
GROUP BY
    Col1

Post #1382335

« Prev Topic | Next Topic »

Permissions