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 soldSELECT 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 QTYThis 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
Query - help required
Posted - 05/16/2012 : 07:29:32
