Doubling Occuring When Summing Over Group

[mordja]

Hi,

I have a very annoying error. I have a query which returns a whole lot of products and sales associated with those products. When I add grouping and sum the sales, all my totals come out double the actual amount initially in the select query.

Any ideas ??

Thanks

 

[mordja]

Whooops,

Here is the SQL from the query.



SELECT DISTINCT Product.ProductDivisionName AS Product, subproduct.ProductDivisionName AS Subproduct, GTSDATA.[L2 Industry Sector], SubIndustry.IndustryName AS L2Industry, GTSDATA.Gfcid, GTSDATA.[Gfcid Name], GFCIDIndustry.IndustryName AS [Gfcid Industry], L3MCC.[Country Name], GTSDATA.[Managed Type], GTSDATA.[L2 Basis Gfcid], GTSDATA.[L2 Basis Gfcid Name], L2MCC.Region AS [L2 Region], L2MCC.[Country Name] AS [L2 Country], Mis.[Country Name], Mis.Cluster, Mis.Region AS [Mis Region], Sum(GTSDATA.[B Prior Yr Jan]) AS [SumOfB Prior Yr Jan], Sum(GTSDATA.[B Prior Yr Feb]) AS [SumOfB Prior Yr Feb], Sum(GTSDATA.[B Prior Yr Mar]) AS [SumOfB Prior Yr Mar], Sum(GTSDATA.[B Prior Yr Apr]) AS [SumOfB Prior Yr Apr], Sum(GTSDATA.[B Prior Yr May]) AS [SumOfB Prior Yr May], Sum(GTSDATA.[B Prior Yr Jun]) AS [SumOfB Prior Yr Jun], Sum(GTSDATA.[B Prior Yr Jul]) AS [SumOfB Prior Yr Jul], Sum(GTSDATA.[B Prior Yr Aug]) AS [SumOfB Prior Yr Aug], Sum(GTSDATA.[B Prior Yr Sep]) AS [SumOfB Prior Yr Sep], Sum(GTSDATA.[B Prior Yr Oct]) AS [SumOfB Prior Yr Oct], Sum(GTSDATA.[B Prior Yr Nov]) AS [SumOfB Prior Yr Nov], Sum(GTSDATA.[B Prior Yr Dec]) AS [SumOfB Prior Yr Dec], Sum(GTSDATA.[B Prior Yr Fyr]) AS [SumOfB Prior Yr Fyr], Sum(GTSDATA.[B Balance Jan]) AS [SumOfB Balance Jan], Sum(GTSDATA.[B Balance Feb]) AS [SumOfB Balance Feb], Sum(GTSDATA.[B Balance Mar]) AS [SumOfB Balance Mar], Sum(GTSDATA.[B Balance Apr]) AS [SumOfB Balance Apr], Sum(GTSDATA.[B Balance May]) AS [SumOfB Balance May], Sum(GTSDATA.[B Balance Jun]) AS [SumOfB Balance Jun], Sum(GTSDATA.[B Balance Jul]) AS [SumOfB Balance Jul], Sum(GTSDATA.[B Balance Aug]) AS [SumOfB Balance Aug], Sum(GTSDATA.[B Balance Sep]) AS [SumOfB Balance Sep], Sum(GTSDATA.[B Balance Oct]) AS [SumOfB Balance Oct], Sum(GTSDATA.[B Balance Nov]) AS [SumOfB Balance Nov], Sum(GTSDATA.[B Balance Dec]) AS [SumOfB Balance Dec], Sum(GTSDATA.[B Balance Fyr]) AS [SumOfB Balance Fyr]
FROM ((ProductDivisions AS subproduct RIGHT JOIN (((APSummaryLines RIGHT JOIN ((CountryNames AS Mis RIGHT JOIN (GTSDATA LEFT JOIN IndustryNames AS SubIndustry ON GTSDATA.[L2 Industry Sub Sector] = SubIndustry.IndustryId) ON Mis.CountryID = GTSDATA.[Mis Country]) LEFT JOIN CountryNames AS L2MCC ON GTSDATA.[L2 Country Managed From] = L2MCC.CountryID) ON APSummaryLines.ApSummaryLineID = GTSDATA.[Ap Summary Line]) LEFT JOIN ProductDivisions AS Product ON GTSDATA.[Product Division] = Product.ProductId) LEFT JOIN IndustryNames AS GFCIDIndustry ON GTSDATA.[Gfcid Industry] = GFCIDIndustry.IndustryId) ON subproduct.ProductId = GTSDATA.[Product Family]) LEFT JOIN CountryNames AS L3MCC ON GTSDATA.[Gfcid Managed From] = L3MCC.CountryID) LEFT JOIN AccountMapping ON GTSDATA.[L2 Basis Gfcid] = AccountMapping.Gfcid
WHERE (((GFCIDIndustry.IndustryId)=[gfcid input]))
GROUP BY Product.ProductDivisionName, subproduct.ProductDivisionName, GTSDATA.[L2 Industry Sector], SubIndustry.IndustryName, GTSDATA.Gfcid, GTSDATA.[Gfcid Name], GFCIDIndustry.IndustryName, L3MCC.[Country Name], GTSDATA.[Managed Type], GTSDATA.[L2 Basis Gfcid], GTSDATA.[L2 Basis Gfcid Name], L2MCC.Region, L2MCC.[Country Name], Mis.[Country Name], Mis.Cluster, Mis.Region
HAVING (((GTSDATA.[L2 Industry Sector]) Like [Industry]) AND ((GTSDATA.[Managed Type]) Like "GTS*" Or (GTSDATA.[Managed Type]) Like "GRB C*" Or (GTSDATA.[Managed Type]) Like "GRB G*") AND ((L2MCC.Region)="EUROPE" Or (L2MCC.Region)="JAPAN" Or (L2MCC.Region)="NORTH AMERICA") AND ((Mis.Cluster) Like [Mcluster]));

 

[PHV]

As you mix Right and Left joins, I suggest you create a query without group by to see why the records are selected two times.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mordja]

PHV,

In order to uncover the problem Ive simplified it down a little. I have my data table GTSDATA which stores revenue figures and numbers which represent product and industry etc I attach a lot of tables to extract the names for those products and industries. In the above I attach productdivisions twice to get the name of the subproduct and the product. I removed all tables until I only had the product attached once to get the product name. This gave me doubles. When I attached it to subproduct instead I had no doubles !

This SELECT DISTINCT GTSDATA.[L2 Industry Sector], GTSDATA.Gfcid, GTSDATA.[Gfcid Name], GTSDATA.[Managed Type], GTSDATA.[L2 Basis Gfcid], GTSDATA.[L2 Basis Gfcid Name], GTSDATA.[B Prior Yr Jan], GTSDATA.[B Prior Yr Feb], GTSDATA.[B Prior Yr Mar], GTSDATA.[B Prior Yr Apr], GTSDATA.[B Prior Yr May], GTSDATA.[B Prior Yr Jun], GTSDATA.[B Prior Yr Jul], GTSDATA.[B Prior Yr Aug], GTSDATA.[B Prior Yr Sep], GTSDATA.[B Prior Yr Oct], GTSDATA.[B Prior Yr Nov], GTSDATA.[B Prior Yr Dec], GTSDATA.[B Prior Yr Fyr], GTSDATA.[B Balance Jan], GTSDATA.[B Balance Feb], GTSDATA.[B Balance Mar], GTSDATA.[B Balance Apr], GTSDATA.[B Balance May], GTSDATA.[B Balance Jun], GTSDATA.[B Balance Jul], GTSDATA.[B Balance Aug], GTSDATA.[B Balance Sep], GTSDATA.[B Balance Oct], GTSDATA.[B Balance Nov], GTSDATA.[B Balance Dec], GTSDATA.[B Balance Fyr], GTSDATA.[Product Division], ProductDivision.ProductDivisionName
FROM ProductDivision RIGHT JOIN GTSDATA ON ProductDivision.ProductId = GTSDATA.[Product Division]
WHERE (((GTSDATA.Gfcid)=50601));



gives me no doubling, all unique records and when I sum it I as you would expect get the right result. However as soon as I add grouping the doubling occurs.

How is it possible that a select query which returns unique rows then duplicates when a grouping is added ??

Thanks

Mordja

 

[JonFer]

You are using DISTINCT in the Select query which removes the duplicates that are generated because there are one or more additional fields that you need to join on. Take out DISTINCT and then see why you have duplicates. Don't add the GROUP BY until the duplicates are eliminated.

 

[PHV]

If you can't figure out how to eliminate the duplicates, then save the SELECT DISTINCT query as, say qryUniqBalances, and then build your aggregate query on it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mordja]

Hey,

Ok, I thought that by using distinct your group by would be based on a distinct set but that obviously is not the case. Ill have a look without the distinct and see. Thanks.

Mordja