Count distinct instances of a SKU and count all items in all instances containing that SKU 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 &quot;Order_Count&quot;  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.
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.