SQL: Selecting rows contained in a group
You join that back to the main table.
SELECT B.ClaimID, B.ClaimDate, B.ClientName, G.ClaimCountFROM( SELECT ClientName, COUNT(ClaimID) ClaimCount FROM Billings WHERE ClaimDate > '2010' Group By ClientName Having COUNT(ClaimID) > 1) GINNER JOIN Billings B on B.ClientName = G.ClientNameWHERE B.ClaimDate > '2010'
Assuming SQL Server 2005 or later you can use a common-table expression
With MultipleBillings As ( Select ClaimId, ClaimDate, ClientName , Count(ClaimId) Over ( Partition By ClientName ) As BillingCount From Billings Where ClaimDate > '2010' )Select ClaimId, ClaimDate, ClientNameFrom MultipleBillingsWhere BillingCount > 1
Select ClaimID, ClientName, Count From Billings Where ClientName In (SELECT ClientName FROM Billings WHERE ClaimDate > '2010' Group By ClientName Having COUNT(ClaimID) > 1) And ClaimDate > '2010'That will generate a list of client names and then select all of the claims that have clients with those names.