How to calculate Total transactions amount column with input / output type?

I have two tables : (transactions and Accounts) their columns are as shown in the picture bellow:

the Accounts table is simple just two columns ID and account name.

The important columns in the Transactions table are Amount and type, the type indicates whether the transaction is an input to the account or an output.

I want to find the current total amount (input – output) for each account with SQL.

this what I have tried but I couldn’t go further:

select c.TRS_AC_ID, CompAccount, sum(Amount), Type from Accounts c 
INNER JOIN Transactions t on c.TRS_AC_ID = t.TRS_AC_ID GROUP by CompAccount, Type

Answer

Instead of grouping by the type, you could use a case expression to return “input” transactions as is and “output” transactions as negative numbers:

SELECT   c.TRS_AC_ID, CompAccount, SUM(CASE type WHEN 'O' THEN -1 * amount ELSE amount END)
FROM     Accounts c 
JOIN     Transactions t on c.TRS_AC_ID = t.TRS_AC_ID
GROUP BY c.TRS_AC_ID, CompAccount