I have data such as:
| Type | Amount |
|---|---|
| a | 1000 |
| a | 5000 |
| b | 4000 |
| b | 2000 |
| c | 300 |
And would like to sum the amounts where Type is a and b, and minus the amounts where type is c.
I only know how to sum based on one condition, ie: select sum(amount) from xxxx where type = 'a'
Do I need to do a sub-select or is there an easier way?
CodePudding user response：
You can use a case statement inside sum:
select sum(case when type in ('a', 'b') then amount when type = 'c' then -amount end)
from table_name;
CodePudding user response：
Use GROUP BY:
SELECT
Type,
sum(Amount)
FROM table
GROUP BY Type
https://www.postgresql.org/docs/10/queries-table-expressions.html#QUERIES-GROUP https://www.postgresql.org/docs/10/tutorial-agg.html
CodePudding user response：
WITH CTE(Type , Amount) AS
(
SELECT 'a' ,1000 UNION ALL
SELECT 'a' , 5000 UNION ALL
SELECT 'b' , 4000 UNION ALL
SELECT 'b' , 2000 UNION ALL
SELECT 'c' , 300
)
SELECT
SUM(CASE WHEN C.TYPE IN ('a','b')THEN C.Amount
ELSE 0
END) -
SUM(CASE WHEN C.TYPE='c' THEN C.Amount
ELSE 0
END)
FROM CTE AS C
CodePudding user response：
for mariaDB
SELECT
debit - credit
FROM
(SELECT sum(Amount) AS debit FROM Your_table WHERE Type IN ('a', 'b')) AS condition1,
(SELECT sum(Amount) AS credit FROM Your_table WHERE Type NOT IN ( 'a', 'b' )) AS condition2;