CODEWITHSUNDEEP
sqlsql-server-2008foreachdistinct
Dion Pedersen
Dion Pedersen

Reputation: 13

SQL TOP 10 sold products, grouped by itemno, grouped by customer

i have a database containing:

Customerno, No_, Quantity

i would like to return a TOP 10 Most sold products per customer in one go...

i can get a TOP 10 like this:

SELECT TOP 10 [No_],sum([Quantity]) as antal 
FROM orderlines
WHERE [Gen_ Prod_ Posting Group] = 'HANDEL' 
AND [Customerno] = 10001
GROUP BY [No_] 
HAVING sum([Quantity]) >= 2
ORDER BY sum([Quantity]) DESC

but i need some kind of "foreach" because the above query is limited to customer: 10001

i would like it to loop foreach distinct customerno, so i recieve a total list

the easy way i know is to make an

SQL query in PHP like this:

while($row = myssql_fetch_array(mssql_query(SELECT DISTINCT Customerno FROM orderlines)))
{

$new_query = ("SELECT TOP 10 [No_],sum([Quantity]) as antal 
FROM orderlines
WHERE [Customerno] = $row['customerno']
GROUP BY [No_] 
HAVING sum([Quantity]) >= 2
ORDER BY sum([Quantity]) DESC");


}

but i feel it should be more efficient to do directly in SQL ?

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

The canonical method uses row_number():

SELECT cn.CustomerNo, cn.[No_], cn.antal
FROM (SELECT CustomerNo, [No_], SUM(Quantity) as antal,
             ROW_NUMBER() OVER (PARTITION BY CustomerNo ORDER BY SUM(Quantity) DESC) as seqnum
      FROM orderlines ol
      WHERE Gen_ Prod_ Posting Group = 'HANDEL' 
      GROUP BY CustomerNo, [No_] 
     ) cn
WHERE seqnum <= 10
ORDER BY CusterNo, antal DESC;

I removed the HAVING condition because that is not part of your problem statement. Obviously, you can filter this based on some minimum antal.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176004

Canonical way is to use ROW_NUMBER. But you could use OUTER APPLY to simulate For-each:

SELECT DISTINCT Customerno, sub.*
FROM orderlines o
OUTER APPLY (SELECT TOP 10 [No_],sum([Quantity]) as antal 
             FROM orderlines o2
             WHERE [Gen_ Prod_ Posting Group] = 'HANDEL' 
                AND o.Customerno = o2.Customerno
             GROUP BY [No_] 
             HAVING sum([Quantity]) >= 2
             ORDER BY antal DESC
            ) AS sub
ORDER BY Customerno, sub.antal DESC

Upvotes: 0

Related Questions