GROUP BY not working - 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

General SQL Server Forums

New to SQL Server Programming

GROUP BY not working

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-04-17 : 19:52:53

This query gives me Customer Sales data. SQL Server 2005. This is grouped by Region and Location. A customer such as 'ABC' should only show up once per Location, but it is pulling in some companies more than once. Can anyone help me figure out how to group this so this gets fixed?

SELECT Region,
Location,
WarehouseCode,
CASE
WHEN CustRank <= 19 THEN CustomerNumber
ELSE 'AllOthers'
END AS CustomerNumber,
CustomerName,
MonthLessEleven,
MonthLessTen,
MonthLessNine,
MonthLessEight,
MonthLessSeven,
MonthLessSix,
MonthLessFive,
MonthLessFour,
MonthLessThree,
MonthLessTwo,
MonthLessOne,
CurrentMonth,
CurrentYearTotal,
LastYearYTD,
LastYearTotal,
CASE
WHEN CustRank <= 19 THEN CustRank
ELSE 20
END AS CustRank
FROM (
SELECT gla.Region,
gla.Location,
ihh.WarehouseCode,
ihh.CustomerNumber,
cm.CustomerName,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD(YEAR, - 1, GETDATE()) > ihh.SOTransDate AND DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,
ROW_NUMBER() OVER (PARTITION BY gla.Region ORDER BY gla.Region, SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) DESC) AS CustRank
FROM MAS_BIF_AR1_CustomerMaster AS cm
INNER JOIN MAS_BIF_ARN_InvHistoryHeader AS ihh ON ihh.CustomerNumber = cm.CustomerNumber
INNER JOIN MAS_BIF_ARO_InvHistoryDetail AS ihd ON ihd.InvoiceNumber = ihh.InvoiceNumber
INNER JOIN

(
SELECT AccountKey,
Account,
CASE SUBSTRING(Account, 5, 3)
WHEN '936' THEN 'North Region'
WHEN '908' THEN 'East Region'
ELSE 'Unknown'
END AS Region,
CASE SUBSTRING(Account, 5, 3)
WHEN '900' THEN 'ALE'
WHEN '902' THEN 'ATO'
WHEN '904' THEN 'BOW'
WHEN '906' THEN 'BRY'
WHEN '908' THEN 'BPT'
WHEN '910' THEN 'BYD'
WHEN '912' THEN 'BUF'
WHEN '914' THEN 'CLE'
WHEN '916' THEN 'GRN'
WHEN '920' THEN 'DXN'
WHEN '924' THEN 'CTH'
WHEN '926' THEN 'ELC'
WHEN '928' THEN 'FTL'
WHEN '930' THEN 'FTW'
WHEN '932' THEN 'I35'
WHEN '936' THEN 'GAI'
WHEN '000' THEN 'GAI'
WHEN '939' THEN 'STW'
WHEN '940' THEN 'GRE'
WHEN '942' THEN 'HEN'
WHEN '944' THEN 'FTS'
WHEN '948' THEN 'JAC'
WHEN '952' THEN 'JEN'
WHEN '956' THEN 'KIL'
WHEN '957' THEN 'MCA'
WHEN '958' THEN 'MIN'
WHEN '960' THEN 'NOC'
WHEN '962' THEN 'ODE'
WHEN '964' THEN 'BTP'
WHEN '966' THEN 'RA'
WHEN '968' THEN 'RIF'
WHEN '970' THEN 'SWD'
WHEN '971' THEN '3PS'
WHEN '972' THEN 'ROC'
WHEN '976' THEN 'SJO'
WHEN '978' THEN 'SMB'
WHEN '980' THEN 'STO'
WHEN '982' THEN 'TOL'
WHEN '984' THEN 'VEL'
WHEN '985' THEN 'CFP'
WHEN '986' THEN 'CLM'
WHEN '988' THEN 'WHI'
WHEN '992' THEN 'WRA'
WHEN '995' THEN 'ADM'
ELSE 'Unknown'
END AS Location
FROM MAS_BIF_GL_Account
) AS gla ON gla.AccountKey = ihd.SOGLSalesAcct
GROUP BY SUBSTRING(Account, 5, 3),gla.Region, gla.Location, ihh.CustomerNumber,
cm.CustomerName,
gla.Account,
ihh.WarehouseCode
) AS x

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 00:51:33

Can you post your full requirement with some sample data. That should make it more easy to understand the problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 01:10:19

You need to greatly simplify the problem for us as your query is just too complex for us to figure out what's going wrong. You need to understand that we aren't familiar with your system.

The way to track down where the problem is in your code is to slowly remove columns, joins, where clause conditions, etc... until you see the problem. Then post the narrowed down query. This way we get to see a simplified query and can easily understand it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources