mysql - need to return two sets of data with two different where clauses

Question

I have a table that keeps track of transactions.

The table is setup as:

transactions:

id, account_id, budget_id, points, type

I need to return each budget_id's sum of points where type = 'allocation' and sum of points where type = 'issue'

I know how to do each, but not both in one query.

expected result set:

budget_id   allocated   issued
   434       200000      100
   242       100000      5020
   621       45000       3940

Answer 1

SELECT budget_id, 
       SUM(IF(type = 'allocation', points, 0)) AS allocated,
       SUM(IF(type = 'issue', points, 0)) AS issued
FROM transactions
GROUP BY budget_id