Clever Geek Handbook

Need to return two datasets with two different locations

I have a table that keeps track of transactions.

The table is configured as:

transactions:

id, account_id, budget_id, points, type

I need to return each budget point sum where type = 'alloc' and point sum where type = 'issue'

I know how to do each, but not both in the same request.

expected result set:

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


source to share


2 answers 


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


+5


source 


    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 only be used to summarize if certain criteria are met.

+2


source






More articles:


All Articles