M21 M21 - 6 months ago 15
SQL Question

SQL top 10 by Brand

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

Answer

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