SQL query for top 10 selling sku's by brand in Volusion

Question

been messing with this query for Volusion store, trying to get top selling sku's by brand.... and I have done so, but how can I also show only the top 10 PER brand....

If I add a top 10 its just 10 rows period.

select
    products_joined.ProductManufacturer as brand,
    Sum(OrderDetails.ProductPrice * OrderDetails.Quantity) AS TotalSold,
    OrderDetails.ProductCode as sku
from 
    orderdetails, orders, products_joined
where 
    products_joined.ProductCode = OrderDetails.ProductCode 
    and Orders.OrderID = OrderDetails.OrderID 
    and Orders.OrderDate BETWEEN getdate() - 90 AND getdate()
    and Orders.OrderStatus <> 'Cancelled' 
    and products_joined.ProductManufacturer is not null
group by 
    products_joined.ProductManufacturer, OrderDetails.ProductCode
order by
    products_joined.ProductManufacturer,
    Sum(OrderDetails.ProductPrice*OrderDetails.Quantity) DESC

I am honestly not sure of anything about the database, I have to write queries on my local system and paste them into a web browser admin to run, and its limited, it cant use variables or temp tables, and I dont have "direct" access to it. It's locked down as a part of the ecommerce platform. — M21, Jun 06 '16 at 20:28
can you run some test queries? like `SELECT Row_number() OVER (Order by ProductManufacturer) as Rn FROM products_joined ` — JamieD77, Jun 06 '16 at 20:34
SELECT Row_number() OVER (Order by ProductManufacturer) as Rn FROM products_joined ####that worked. — M21, Jun 06 '16 at 20:37
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Jun 06 '16 at 20:42

JamieD77 · Accepted Answer · 2016-06-06T20:42:00.573

if ROW_NUMBER is available, you might also be able to use CTE's and do something like this.

;WITH cteProductsSold AS (
    SELECT  pj.ProductManufacturer AS brand,
            od.ProductCode AS sku,
            SUM(od.ProductPrice * od.Quantity) AS TotalSold
    FROM    orders o
            INNER JOIN orderdetails od ON od.OrderID = o.OrderID
            INNER JOIN products_joined pj ON pj.ProductCode = od.ProductCode
    WHERE   o.OrderDate BETWEEN GETDATE() - 90 AND GETDATE()
            AND o.OrderStatus <> 'Cancelled'
            AND pj.ProductManufacturer IS NOT NULL

    GROUP BY pj.ProductManufacturer,
            od.ProductCode
), cteProductOrdered AS (
    SELECT *,
            ROW_NUMBER() OVER (PARTITION BY brand ORDER BY TotalSold DESC) Rn
    FROM    cteProductsSold
)
SELECT  brand,
        sku,
        TotalSold
FROM    cteProductOrdered
WHERE   Rn < 11

alternatively, you can use derived tables instead of CTEs.

SELECT  brand,
        sku,
        TotalSold
FROM    (   SELECT  *,
                    ROW_NUMBER() OVER (PARTITION BY brand ORDER BY TotalSold DESC) Rn
            FROM    (   SELECT  pj.ProductManufacturer AS brand,
                                od.ProductCode AS sku,
                                SUM(od.ProductPrice * od.Quantity) AS TotalSold
                        FROM    orders o
                                INNER JOIN orderdetails od ON od.OrderID = o.OrderID
                                INNER JOIN products_joined pj ON pj.ProductCode = od.ProductCode
                        WHERE   o.OrderDate BETWEEN GETDATE() - 90 AND GETDATE()
                                AND o.OrderStatus <> 'Cancelled'
                                AND pj.ProductManufacturer IS NOT NULL

                        GROUP BY pj.ProductManufacturer,
                                od.ProductCode
                    ) p
        ) ps
WHERE   Rn < 11

JamieD77 - that works. the second thing! - thank you much, now to decipher it so I can understand the part I was missing — M21, Jun 06 '16 at 20:48
@M21 here is a good start https://msdn.microsoft.com/en-us/library/ms189798.aspx ROW_NUMBER is a ranking function that will give each row a number (1 thru however many rows) and it can use Partitions to reset the row number when the partition (group) changes. — JamieD77, Jun 06 '16 at 20:53

score 1 · Answer 2 · answered Jun 06 '16 at 20:45

this should work too

select * from (
select
products_joined.ProductManufacturer as brand,
Sum(OrderDetails.ProductPrice*OrderDetails.Quantity) AS TotalSold,
OrderDetails.ProductCode as sku,
row_number() over ( partition by products_joined.ProductManufacturer, OrderDetails.ProductCode order by Sum(OrderDetails.ProductPrice*OrderDetails.Quantity) desc) rowid

from orderdetails, orders, products_joined

where 
products_joined.ProductCode = OrderDetails.ProductCode and
Orders.OrderID = OrderDetails.OrderID and 
Orders.OrderDate BETWEEN getdate() - 90 AND getdate()
AND Orders.OrderStatus <> 'Cancelled' and products_joined.ProductManufacturer is not null
GROUP BY products_joined.ProductManufacturer, OrderDetails.ProductCode
) as x
where rowid < 11
ORDER BY brand,TotalSold DESC

if you partition by Sku (OrderDetails.ProductCode) every row number will be 1 if you group by `products_joined.ProductManufacturer, OrderDetails.ProductCode` — JamieD77, Jun 06 '16 at 20:54

SQL query for top 10 selling sku's by brand in Volusion

2 Answers2