Top N sort on group - 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

Top N sort on group

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-03 : 13:03:16

I am doing a running 12 month Sales report. Invoice totals are summed. I want to Group By Location and Customer. I would also like to do a Top 20 customers for each location. Here is what I have so far and it comes back with a failure of Invalid column name 'Location'.

I don't know how to do the Top 20 either. Any help would be appreciated. Thanks.

SELECT SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 2 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 3 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 4 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 5 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 6 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 7 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 8 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 9 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 10 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END) AS LastYearTotal,
SUM(CASE WHEN DATEADD([year], - 1, GetDate()) > ih.SOTransDate AND DATEDIFF([YEAR], ih.SOTransDate, GetDate())
= 1 THEN id.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
CASE WHEN a.Account LIKE '400%' THEN 'ALEDO' ELSE ' ' END AS Location,
ih.SOTransDate, ih.InvoiceNumber, cm.CustomerName, id.DetailSeqNumber, id.LineType, a.Account, id.SOExtChargeAmount
FROM dbo.MAS_CCS_AR1_CustomerMaster cm, dbo.MAS_CCS_ARN_InvHistoryHeader ih, dbo.MAS_CCS_ARO_InvHistoryDetail id,
dbo.MAS_CCS_GL_Account a
WHERE cm.CustomerNumber = ih.CustomerNumber AND ih.InvoiceNumber = id.InvoiceNumber AND id.SOGLSalesAcct = a.AccountKey
GROUP BY cm.CustomerName, Location
ORDER BY CurrentYearTotal

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-03 : 13:52:24

You can't group by a the ALIAS of calcualted column, try this GROUP BY:
GROUP BY
cm.CustomerName,
CASE WHEN a.Account LIKE '400%' THEN 'ALEDO' ELSE ' ' END