Sql-server – Filter customers with negative balance across multiple accounts

sql serversql-server-2012

The company I'm working for allows their customers to have multiple accounts. It's also possible for an account to be in the negative. So, their customer balance table looks like this:

┌──────────────┬─────────┬─────────┐
│ Customer No. │ Account │ Balance │
├──────────────┼─────────┼─────────┤
│           1  │       A │    1.00 │
├──────────────┼─────────┼─────────┤
│           1  │       B │   -2.00 │
├──────────────┼─────────┼─────────┤
│           1  │       C │    3.00 │
├──────────────┼─────────┼─────────┤
│           2  │       A │    4.00 │
├──────────────┼─────────┼─────────┤
│           2  │       B │   -5.00 │
├──────────────┼─────────┼─────────┤
│           2  │       C │   -6.00 │
├──────────────┼─────────┼─────────┤
│           3  │       A │    7.00 │
└──────────────┴─────────┴─────────┘

I'm trying to get a sum total of the balance column, excluding any customers that have a negative total balance. So, the result from the table above should be 9.00 — Customers 1 and 3 have a positive total balance, so all their accounts are included, but customer 2 has a negative total balance, so none of their accounts are included.

(Note: This is a once-a-year query, so performance isn't really an issue.)

Best Answer

This is a very simple query. To get the total balance of each customer (with non-negative total):

SELECT customer_no, SUM(balance) AS total_balance
FROM accounts
GROUP BY customer_no
HAVING SUM(balance) >= 0 ;

To get the total sum of all the above customer balances:

SELECT SUM(total_balance) AS super_total_balance
FROM
  ( SELECT SUM(balance) AS total_balance
    FROM accounts
    GROUP BY customer_no
    HAVING SUM(balance) >= 0
  ) AS t ;

or with a rather cryptic variation:

SELECT TOP (1)
       SUM(SUM(balance)) OVER () AS super_total_balance
FROM accounts
GROUP BY customer_no
HAVING SUM(balance) >= 0 ;
Related Question