Geek Answers Handbook

SQL: selecting rows contained in a group

I am working on a query for SQL Server, and I am wondering if anyone can give me some advice on choosing the individual rows included in the group (where the group is based on an aggregated function, COUNT in this case)

So, as a simplified example, if I have a table of accounts, as shown below, I want to select all accounts for each client, where they have 2 or more accounts 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 do

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

I would get:

 Jim 2 Mary 3

Which is good, he finds all customers who have 2 or more accounts in a period of time, but I want to actually list what billions 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 do this?

Thanks.

+6
sql sql-server
source share
3 answers

You join it 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'
+8
source share

Assuming SQL Server 2005 or later, you can use a shared 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
+4
source share
 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'

This will create a list of customer names and then select all claims that have customers with these names.

+1
source share

More articles:


All Articles