CODEWITHSUNDEEP
sqlsql-servergroup-bycounthaving
user10301497
user10301497

Reputation: 39

SQL How would I group records based on conditions?

I'm trying to find any customers with a total of at least 4 accounts with at least 1 account that has been open in the last 6 months.

So far I'm able to get the customer and total accounts, but I'm not sure how to add a column for the number of new accounts open in the last 6 months. 

SELECT CustomerID
       ,COUNT(AccountID) as 'Total Accounts'
FROM Customer
GROUP BY CustomerID
HAVING COUNT(AccountID) >= 3`

Here are my tables:

Customer

and

Accounts

And here is what my final result should look like. so far I have CustomerID and Total Accounts. 

CustomerID | Total Accounts | New Accounts |
-----------+----------------+------------- +
    Ben    |       3        |      1       |

EDIT: The program I'll be importing this query in doesn't allow case statements.

Upvotes: 0

Views: 64

Answers (4)

sticky bit
sticky bit

Reputation: 37472

You can left join the new accounts. Include the filter for startdate in the join condition. For accounts, that don't satisfy that condition no record from account is joined. So the account ID is null for them and won't get counted.

SELECT c.customerid,
       count(c.accountid) "Total Accounts",
       count(a.accountid) "New Accounts"
       FROM customer c
            LEFT JOIN account a
                      ON a.accountid = c.accountid
                         AND a.startdate >= dateadd(month, -6, getdate())
       GROUP BY c.customerid
       HAVING count(c.accountid) >= 4
              AND count(a.accountid) >= 1;

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453243

The program I'll be importing this query in doesn't allow case statements.

For your stated goal of finding customers with at least 4 accounts and at least 1 account that has been opened in the last 6 months you don't need the count of recent accounts anyway. It is sufficient to get the latest account date opened and see if that was in the last 6 months.

SELECT c.CustomerId
FROM   Customer c
       JOIN Accounts a
         ON c.AccountId = a.AccountId
GROUP  BY c.CustomerId
HAVING COUNT(*) >= 4
       AND MAX(a.StartDate) >= DATEADD(MONTH, -6, GETDATE())

Upvotes: 1

Mureinik
Mureinik

Reputation: 311228

Count, like most aggregate functions, ignores nulls. I'd use this property by joining the two tables and using a case expression to return only the new accounts:

SELECT   CustomerId, 
         COUNT(*) AS TotalAccounts,
         COUNT(CASE WHEN DATEDIFF(MONTH, StartDate, GETDATE()) <= 6 THEN 1 END) 
           AS NewAccounts
FROM     Customer c
JOIN     Accounts a ON c.AccountId = a.AccountId
GROUP BY CustomerId
HAVING   COUNT(*) >= 4 AND
         COUNT(CASE WHEN DATEDIFF(MONTH, StartDate, GETDATE()) <= 6 THEN 1 END) > 0

Upvotes: 2

Hogan
Hogan

Reputation: 70523

This query will give you the total account count and the number that is new.

SELECT C.CustomerID,
       COUNT(C.AccountID) as Total_Acc,
       SUM(CASE WHEN DATEADD(MONTH,-6,GETDATE()) <= A.StarDate) THEN 1 ELSE 0 END) as New_Acct
FROM Customer C
LEFT JOIN Account A ON C.AccountID = A.AccountID
GROUP BY CustomerID

You can then use that in a sub-query to find what you want

eg 

SELECT * 
FROM (
  SELECT C.CustomerID,
       COUNT(C.AccountID) as Total_Acc,
       SUM(CASE WHEN DATEADD(MONTH,-6,GETDATE()) <= A.StarDate) THEN 1 ELSE 0 END) as New_Acct
  FROM Customer C
  LEFT JOIN Account A ON C.AccountID = A.AccountID
  GROUP BY CustomerID
) SUB
WHERE Total_Acc >= 4 and New_Acct >= 1

Upvotes: 1

Related Questions