Get the date for each duplicated row in SQL Server-CodePudding

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?

CodePudding user response：

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