I have a transactions
table which contains a category (category_id
), an amount (amount
) and a flag (managed
) which can be true or false.
I would like to display a list of all categories with the total amounts of managed and not managed transactions, e.g.
Category | managed_transactions | unmanaged_transactions
Cat 1 | 124000 | 54000
Cat 2 | 4000 | 0
Cat 3 | 854000 | 1000000
Is there a way to do something like
Select category_id,
sum(amount) if (managed is true) as managed_transactions,
sum(amount) if (managed is false) as unmanaged_transactions
from transactions
I'm obviously stuck on the if managed is true
part...
Use the SUM() function to calculate the sum of values. Use the DISTINCT option to calculate the sum of distinct values. Use the SUM() function with the GROUP BY clause to calculate the sum for each group.
In PostgreSQL, the ADD COLUMN command/statement along with the ALTER TABLE clause is used to add single or multiple columns to a table. The ADD COLUMN command allows us to add new columns with constraints such as DEFAULT, NOT NULL, UNIQUE, etc.
If you know the values to always be the same, then one simple (although not necessarily optimized) solution is to wrap your original GROUP BY query in another query (making the original a subquery) and then use the SUM function in the outer query, without a GROUP BY clause.
Enjoy!
SELECT
category_id,
SUM( CASE WHEN managed THEN amount ELSE 0 END ) AS managed_transactions,
SUM( CASE WHEN managed THEN 0 ELSE amount END ) AS unmanaged_transactions
FROM
transactions
GROUP BY
category_id
ORDER BY
category_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With