3

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
Leo
  • 6,342
  • 4
  • 34
  • 50
Brad
  • 11,601
  • 44
  • 114
  • 180
  • Subqueries in either the field list or a join to a subquery – scrowler Aug 11 '14 at 23:42
  • E.g. to get you started `SELECT budget_id, A.all_sum AS allocated, I.iss_sum AS issued FROM transactions INNER JOIN (SELECT SUM(points) AS all_sum FROM transactions WHERE type='Allocation') AS A INNER JOIN (SELECT SUM(points) FROM transactions WHERE type='Issue') AS I` – scrowler Aug 11 '14 at 23:49
  • @scrowler - will work, but you can use case to conditional sum if the grouping is the same. – Twelfth Aug 11 '14 at 23:51
  • Hi Brad. Did either of the below answers assist you? If so, please consider marking one of them as accepted. – halfer Feb 20 '15 at 10:03

2 Answers2

6
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
Barmar
  • 669,327
  • 51
  • 454
  • 560
2
    select budget_ID, 
     sum(case when type = 'allocated' then points else 0 end) as allocated,
     sum(case when type = 'issued' then points else 0 end) as issued
     ..rest of your query...
    group by budget_ID

Cases can be used to sum only when a certain criteria is met.

Lance Roberts
  • 21,757
  • 30
  • 108
  • 129
Twelfth
  • 6,822
  • 2
  • 25
  • 34