SQLTeam.com | Weblogs | Forums

SUM based on column value change

Hello, I am trying to calculate a SUM based on a change in Account number. For example, a bank has a branch with 2 accounts. Each account has individual members with individual checking and savings accounts.

So instead of returning each individual SUM of the account name and member number, I want to SUM
based on each change in Account name.

I am having trouble with calculating the SUM based on each change of the Account Name

For example I am looking to SUM the Checking totals and Savings totals for Account 12345 and 44444. Which would include the 8 members of Account 12345 and the 7 Members of Account 44444.

|AccountA|Member Number|Checking|Savings|

|12345|01|$55.00|$545.00|
|12345|02|$54.00|$7,747.00|
|12345|03|$120.00|$555.00|
|12345|04|$555.00|$77.00|
|12345|05|$4.00|$66.00|
|12345|06|$35.00|$55.00|
|12345|07|$54.00|$44.00|
|12345|08|$587.00|$65.00|
|44444|01|$7,865.00|$7.00|
|44444|02|$66.00|$5.00|
|44444|03|$55.00|$6.00|
|44444|04|$44.00|$4.00|
|44444|05|$77.00|$2.00|
|44444|06|$66.00|$1.00|
|44444|07|$33.00|$2.00|

|Return SUM||||
|12345||$1,464.00|$9,154.00|
|44444||$8,206.00|$27.00|

|Return SUM > $9000.00||||
|12345|||$9,154.00|

So basically return the total based on each account Name and Group those together.
In my table, the Account Name and Member number are in different columns.

Then, from that SUM, I want to only return those records that have Savings > $9000.00

Thank you in advance.

Maybe this?

select AccountA, sum(Savings)
  from #bank
  group by AccountA
  having sum(Savings) > 9000.00