CODEWITHSUNDEEP
sqlsql-server
Luke
Luke

Reputation: 1258

SQL: Selecting rows contained in a group

I'm working on a query for SQL Server and I'm wondering if anyone can give me some tips on selecting individual rows that make up a group (where the group's based on an aggregate function, COUNT in this case)

So, as a simplified example, if I have a table of billings, as below, I want to select all billings for each client where they have 2 or more billings after a certain date.

ClaimID     ClaimDate             ClientName
101         May 5, 2010           Jim
102         June 19, 2010         Jim
103         August 5, 2008        Jim
104         January 1, 2011       Mary
105         May 8, 2009           Mary
106         November 4, 2010      Mary
107         October 6, 2010       Mary
108         April 4, 2010         Bob
109         April 29, 2009        Bob
110         July 7, 2006          Joe

So if I execute

SELECT ClientName, COUNT(ClaimID) FROM Billings
WHERE ClaimDate > '2010'
Group By ClientName
Having COUNT(ClaimID) > 1

I'd get:

Jim  2
Mary    3

Which is good, it finds all clients who have 2 or more billings in the time frame, but I want to actually list what those billings are. So I want this: 

ClaimID ClientName  Count
101         Jim     2
102         Jim     2
104         Mary    3
106         Mary    3
107         Mary    3

What do you think is the best way to accomplish this?

Thanks.

Upvotes: 14

Views: 14066

Answers (3)

Thomas
Thomas

Reputation: 64635

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, ClientName
From MultipleBillings
Where BillingCount > 1

Upvotes: 6

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

You join that back to the main table.

SELECT B.ClaimID, B.ClaimDate, B.ClientName, G.ClaimCount
FROM
(
    SELECT ClientName, COUNT(ClaimID) ClaimCount
    FROM Billings
    WHERE ClaimDate > '2010'
    Group By ClientName
    Having COUNT(ClaimID) > 1
) G
INNER JOIN Billings B on B.ClientName = G.ClientName
WHERE B.ClaimDate > '2010'

Upvotes: 19

msarchet
msarchet

Reputation: 15232

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.

Upvotes: 1

Related Questions