CODEWITHSUNDEEP
sqlsql-server
Al Simmons
Al Simmons

Reputation: 17

Get the date for each duplicated row in SQL Server

I've made a query to get how many products are sold more than one time and it worked.

Now I want to show the transaction date for each of these duplicated sales, but when I insert the date on the select it brings me a lot less rows: something is going wrong. The query without the date returns 9855 rows and with the date just 36 rows.

Here is the query I'm doing:

SELECT TransactionDate, 
       ProductName, 
       QtyOfSales = COUNT(*) 
FROM product_sales
WHERE ProductID = 1 -- Product Sold ID
  AND ProductName IS NOT NULL 
GROUP BY ProductName, 
         TransactionDate
HAVING COUNT(*) > 1

Perhaps a subquery? Can you help in that regard?

Upvotes: 0

Views: 39

Answers (1)

lemon
lemon

Reputation: 15502

You can use the corresponding COUNT window function, that will find the amount of transactions by partitioning on the "ProductName" as required:

WITH cte AS(
       SELECT TransactionDate,
              ProductName, 
              COUNT(*) OVER(PARTITION BY ProductName) AS QtyOfSales 
       FROM product_sales
       WHERE ProductID = 1 -- Product Sold ID
         AND ProductName IS NOT NULL 
)
SELECT DISTINCT TransactionDate,
                ProductName 
FROM cte 
WHERE QtyOfSales > 1

Upvotes: 1

Related Questions