CODEWITHSUNDEEP
sql-serverwindow-functions
user586399
user586399

Reputation:

select count over partition by

I am learning window functions in sql server. I am using AdventrueWorks2012 database for practice. I want to calculate total number of sales and purchases for each item in the store.

The classic solution can be like

    SELECT ProductID,
       Quantity,
       (SELECT Count(*)
        FROM   AdventureWorks.Purchasing.PurchaseOrderDetail
        WHERE  PurchaseOrderDetail.ProductID = p.ProductID) TotalPurchases,
       (SELECT Count(*)
        FROM   AdventureWorks.Sales.SalesOrderDetail
        WHERE  SalesOrderDetail.ProductID = p.ProductID)    TotalSales
FROM   (SELECT DISTINCT ProductID,
                        Quantity
        FROM   AdventureWorks.Production.ProductInventory) p

Trying to convert to window functions gives me wrong results:

    SELECT DISTINCT d.ProductID,
                Quantity,
                Count(d.ProductID)
                  OVER(
                    PARTITION BY d.ProductID)  TotalPurchases,
                Count(d2.ProductID)
                  OVER(
                    PARTITION BY d2.ProductID) TotalSales
FROM   (SELECT DISTINCT ProductID,
                        Quantity
        FROM   AdventureWorks.Production.ProductInventory) p
       INNER JOIN AdventureWorks.Purchasing.PurchaseOrderDetail d
               ON p.ProductID = d.ProductID
       INNER JOIN AdventureWorks.Sales.SalesOrderDetail d2
               ON p.ProductID = d2.ProductID
ORDER  BY d.ProductID

Why this is wrong? How can I correct it?

Upvotes: 1

Views: 1346

Answers (1)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

You should change INNER JOIN to LEFT JOIN

Because when you inner join, result will miss productid which from ProductInventory table does not have PurchaseOrderDetail or SalesOrderDetail.

Upvotes: 1

Related Questions