add and subtract by type

Question

I have a SQLite table payments:

+------+--------+-------+
| user | amount | type  |
+------+--------+-------+
| AAA  |  100   | plus  |
| AAA  |  200   | plus  |
| AAA  |  50    | minus |
| BBB  |  100   | plus  |
| BBB  |  20    | minus |
| BBB  |  5     | minus |
| CCC  |  200   | plus  |
| CCC  |  300   | plus  |
| CCC  |  25    | minus |

I need to calculate the sum with type 'plus' and subtract from it the sum with type 'minus' for each user.

The result table should look like this:

+------+--------+
| user | total  | 
+------+--------+
| AAA  |  250   | 
| BBB  |  75    | 
| CCC  |  475   | 

I think that my query is terrible, and I need help to improve it:

select user,
(select sum(amount) from payments as TABLE1 WHERE TABLE1.type = 'plus' AND
 TABLE1.user= TABLE3.user) -
 (select sum(amount) from payments as TABLE2 WHERE TABLE2.type = 'minus' AND
TABLE2.user= TABLE3.user) as total
from payments as TABLE3
group by client
order by id asc

Answer 1

The type is easier handled with a CASE expression. And then you can merge the aggregation into the outer query:

SELECT user,
       SUM(CASE type
           WHEN 'plus'  THEN amount
           WHEN 'minus' THEN -amount
           END) AS total
FROM payments
GROUP BY client
ORDER BY id;