Grouping and Subtracting Values from the Same Column of Data

Other SQL Server Topics
1

I am relatively new to SSMS. I am attempting to write a query to sum balances with specific account numbers, and then subtract the sum of the 2nd group from the sum of the first group. I am only concerned with the totals of the underlying balances, but need those balances based on the account numbers. Here's an example of how the data is structured:

AcctNo BAL
123 11
234 22
345 33
456 44
678 55
789 66

I would like to sum the balances of group one, and group 2, and subtract group 2 from group 1. My initial attempts were as follows:
Declare @Bal1 INT
Declare @Bal2 INT
Select @Bal1 = Sum(Bal)
From XXX
Where Acctno In ('123','234','345')

Select@Bal2 = sum(Bal)
From XXX
Where Acctno IN ('678','789')

@Bal1 - @Bal2

Any insight you can provide would be greatly appreciated. Thank you.

2

How do you determine what is in group 1 and what is in group 2? I am not able to discern that neither from the AcctNo column nor from the Bal column.

1 Like
3

If you want to hard-code the groups, you can use the code below. It would probably be more consistent if you created a table with the Acctno groupings:

SELECT
    SUM(CASE WHEN Acctno IN ('123', '234', '345') THEN Bal ELSE 0 END) AS Bal_123,
    SUM(CASE WHEN Acctno IN ('678', '789') THEN Bal ELSE 0 END) AS Bal_678
FROM XXX
1 Like
4

Thank you this result worked. I had to manually discern what applies to each group based off of general ledger account codes, which apply to loan balances and interest expenses, each being their own individual subset, which there was no way to identify without entering each of them individually to be included in each of the respective groups. I appreciate the help immensely.