Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

GROUP BY Error Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 1:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:43 AM
Points: 174, Visits: 278
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
Posted Thursday, November 8, 2012 1:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:53 AM
Points: 5,218, Visits: 5,072
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
Posted Thursday, November 8, 2012 2:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:43 AM
Points: 174, Visits: 278

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
Posted Thursday, November 8, 2012 2:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:53 AM
Points: 5,218, Visits: 5,072
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





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 #1382335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse