How do I return records where the sum of a column that corresponds to a specific user ID is greater than 1M?

user_15419195 0
Viewed 49

My scenario is as follows -

I have these records in my DB table (Table name: MyTable):

ID                                      ACCOUNT_NUM     DATE        BALANCE
------------------------------------    -----------     ----------  -----------
111                                     1               2020-12-17  500000.0000
111                                     2               2020-12-17  500000.0000
222                                     1               2020-12-17  500000.0000
222                                     2               2020-12-17  400000.0000
333                                     1               2020-12-17  800000.0000
333                                     2               2020-12-17  300000.0000
444                                     1               2020-12-17  1000000.0000
444                                     2               2020-12-17  100.0000

Each ID represents a member and each member has 2 accounts.

I need to return only the records which have an aggregated balance of over 1000000.00 (1M) by adding account 1 and 2 for each ID.

Ive tried this so far:

SELECT ID, ACCOUNT_NUM, DATE, BALANCE FROM MyTable
GROUP BY ID, ACCOUNT_NUM, DATE, BALANCE 
HAVING SUM(BALANCE) >= '1000000.00';

But it is only returning a single record for ID 444 where the account 1 balance is = 1M.

How can I get the query to SUM accounts 1 and 2 for each member and return if over 1M?

New to SQL. Please let me know if I need to add more information.

Thanks in advance

db2 sql
2 Answers

Use window functions:

SELECT ID, ACCOUNT_NUM, DATE, BALANCE
FROM (SELECT t.*,
             SUM(BALANCE) OVER (PARTITION BY ID, ACCOUNT_NUM, DATE) as SUM_BALANCE
      FROM MyTable t
     ) t
WHERE SUM_BALANCE >= 1000000.00;

Your question suggests that you just want to do the sum by account_num but your current logic includes other columns. I have basically included your current logic -- although it does not make sense to include BALANCE because that is what you are adding up.

user_11440350

Use a sub-query to apply the necessary criteria. In this example, as each row from the table (aliased as "t1") is evaluated, it will check against the sum total of all records in the table (aliased as "t2", via the sub-query) that have the same "ID" value

SELECT ID, ACCOUNT_NUM, DATE, BALANCE FROM MyTable t1
WHERE
(
    SELECT SUM(BALANCE) FROM MyTable t2
    WHERE t2.ID = t1.ID
) > 1000000.00
user_154040090
Related