CODEWITHSUNDEEP
sql
Bongsky
Bongsky

Reputation: 503

SQL Query Filtering According to Status

I have a table called accounts... in the accounts table the columns are:

-account names
-account status
-balance
-account grp

The account status is only compose of either Current or Past Due... I have managed to select the distinct Account Grp and the sum of all the balances of each account names that belongs to the particular account group... My problem is i also want to get the total balance of the accounts with the account status of current and past due that when added it will result to the total balance

Just like the output below

                                       Account Status
Account Grp |   Total Balance   |   Current  |  Past Due  |
  1001      |       500         |     250    |     250    | 
  1003      |       150         |     100    |      50    |
  1003      |       100         |     25     |      75    |

Upvotes: 0

Views: 141

Answers (1)

Jade
Jade

Reputation: 2992

Try this

SELECT [account grp], 
       [account names], 
       Sum(balance) [Total balance], 
       Sum(CASE [account status] 
             WHEN 'Current' THEN balance 
             ELSE 0.00 
           END)     CURRENT, 
       Sum(CASE [account status] 
             WHEN 'Past' THEN balance 
             ELSE 0.00 
           END)     Past 
FROM   [accounts] 
GROUP  BY [account grp], 
          [account names]

Upvotes: 2

Related Questions