Menu
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.
217
select top (1) with ties r. * from runner r order by min(rtime) over (partition by gid), gid; At least, this will get the complete first group. In any case, the idea is to include gid as a key in the order by and to use top with ties .
To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
You can use a SELECT command with a GROUP BY clause to group all rows that have identical values in a specified column or combination of columns, into a single row. You can also find the aggregate value for each group of column values.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
