Query - help required - 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

Query - help required

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-05-16 : 07:29:32

I have to build a customers report containing for every single customer the 3 top items he bought - considering both sales totals and amount of items sold. A line should contain:

Customer, Top1st by annual turnover, Top2nd by annual turnover,
Top3rd by annual turnover, Top1st by amount sold, Top2nd by amount sold, Top3rd by amount sold

SELECT   TOP (3) Customers.NAME, Articles.ITEMID, COUNT(Articles.ITEMID) AS QTY, SUM(Invoice.LINEAMOUNT) AS Turnover
FROM     Customers LEFT OUTER JOIN
         Invoice ON Customers.ACCOUNT = Invoice.ACCOUNT LEFT OUTER JOIN
         Articles ON Invoice.ITEMID = Articles.ITEMID
WHERE Customers.ACCOUNT = '10206500'
GROUP BY Articles.ITEMID, Customers.NAME
ORDER BY QTY

This query gets the TOP3 by quantity; Ordering by Turnover will get me the TOP3 by turnover. But this is just ONE single specific customer! I have to pack all this information in one single line and do it for all the 100 customers of that table... makes me dizzy just thinking of it.

Any help?
Best regards,
Martin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-16 : 08:31:35

Pick any of the methods
http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-05-16 : 11:31:30

Thank you Madhivanan. I realy can use these queries. I choose the row number methode, because the row number works fine as the column index number to later link the row values to a column; as I have to have just a single row, carrying all information on the top selling items.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources