SQL Server Aggregate Query

Question

I need to write a query that returns the ID of the items counted to find duplicates.

I have a Transaction table with Procedures and need to find accounts where COUNT(Procedure)>1 GROUP BY Account. That part I can do. I don't know how to return the primary key IDs of the records used in the COUNT.

Answer 1

SELECT *
FROM xxxx
WHERE Account IN (
    SELECT Account
    FROM xxxx
    GROUP BY Account
    HAVING COUNT(Proceedure)>1
)

Answer 2

So you want to refer to detailed rows, lost by the aggregation? Than you can't use just one query. You can for instance join your GROUP BY query to another query that get the detailed data. No need to store the intermediate stuff in a temp table, you can use derived table or CTE. Without DDL and an executable example, it is about as far as we can go.

Answer 3

Hi @SM ,
I am very happy that your problem has been solved.
If you have any question, please feel free to let me know.

Best Regards
Echo