0

This is on MSSQL/Azure SQL.

I have the following Sales Table:

Line_Item_ID Sales_Order_ID SKU
1 1 4
2 1 1
3 2 4
4 3 1
5 3 7
6 3 4
7 4 4
8 5 1
9 5 8

I am trying to get an output that shows for each SKU, the total number of Orders (very easy) AND the total number of line items for an Order that contains that SKU.

So for the first part:

select SKU, count(distinct(sales_order_id) as "Order_Count"  from Table T
group by SKU

Easy enough, however the second part is doing my head in -

The desired output would look like this:

SKU Order_Count Line_Item_Count
1 3 7
4 4 7
7 1 3
8 1 2

How do I get a count of the total Line_item_IDs for each order that contains a SKU? Ideally in a single query without a load of crazy joins or subqueries.

1

You can use a window function with the COUNT() function in a CTE or subquery to get the total Line_Item_ID count per Sales_Order_Id or even just group it separately at that point, and re-join it to your existing query with a SUM() on top of it to get the total Line_Item_ID for each SKU like so:

WITH CTE_LineItemsPerSalesOrder AS
(
    SELECT sales_order_id, COUNT(1) AS LineItemsPerSalesOrderCount
    FROM Table
    GROUP BY sales_order_id
)

SELECT T.SKU, COUNT(DISTINCT(T.sales_order_id) as Order_Count, 
    SUM(C.LineItemsPerSalesOrderCount) AS Line_Item_Count
FROM Table T
INNER JOIN CTE_LineItemsPerSalesOrder AS C
    ON T.sales_order_id = C.sales_order_id
GROUP BY T.SKU
ORDER BY T.SKU

Feel free to rename and reformat the casing of the syntax I used, as needed.

1

Depending on available indexes and size of data, you may find this useful

SELECT
    x.Line_Item_ID, 
    x.Sales_Order_ID, 
    x.SKU
INTO #x
FROM
(
    VALUES
        (1, 1, 4),
        (2, 1, 1),
        (3, 2, 4),
        (4, 3, 1),
        (5, 3, 7),
        (6, 3, 4),
        (7, 4, 4),
        (8, 5, 1),
        (9, 5, 8)
) AS x
(Line_Item_ID,  Sales_Order_ID, SKU);


SELECT
    x.SKU,
    COUNT_BIG(DISTINCT x.Sales_Order_ID) AS OrderCount,
    SUM(l.SaleCount) AS LineItemCount
FROM #x AS x
CROSS APPLY
(
    SELECT
        COUNT_BIG(*) SaleCount
    FROM #x AS x2
    WHERE x.Sales_Order_ID = x2.Sales_Order_ID
    GROUP BY x2.Sales_Order_ID
) AS l
GROUP BY x.SKU
ORDER BY x.SKU;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.