Mysql group by and sum two different columns-CodePudding

I have this table called transactions, where agents can give certain amounts to other agents, we have 2 columns, one called agent_from which is the agent that put the amount and agent_to is the one reciving the amount.

An example with the id 1 would be that the agent2 is giving an amount of 300 to the agent8

The report that I would like to do is a sum and a group by agent_from and agent_to

Right now I am able to make the query separatly like this

    SELECT agent_from,
      SUM(amount) as from_transaccions
    FROM `transactions` GROUP BY agent_from;

This would give me this result:

This return a sum of all the amounts made by agent_from. Now I can repeat this query changing the column name from agent_from to agent_to so I can get the sum of all the amounts recived by agent_to, that will look like this:

An example would be that the agent8 recived 2 transaccions (300 450) = 750

Now what I want to do is make this 2 querys into one that will look like this:

CodePudding user response：

Refer query below -

with data_cte as (
   (select agent_from agent, amount, 'af' flag from transactions) union all
   (select agent_to agent, amount, 'at' flag from transactions)
)
select agent, 
    sum(case when flag='af' then amount else 0 end) from_sum,
    sum(case when flag='at' then amount else 0 end) to_sum
from data_cte
group by agent
union all
select 'total' as col1, 
    sum(case when flag='af' then amount else 0 end) from_sum,
    sum(case when flag='at' then amount else 0 end) to_sum
from data_cte
group by col1
order by agent

fiddle.

CodePudding user response：

Use UNION ALL to split each row of the table to 2 rows so that you separate the 2 agents and aggregate:

SELECT COALESCE(agent, 'total') agent, 
       SUM(`from`) `from`, 
       SUM(`to`) `to`
FROM (
  SELECT agent_from agent, amount `from`, 0 `to` FROM `transactions`
  UNION ALL
  SELECT agent_to, 0 `from`, amount `to` FROM `transactions`
) t
GROUP BY agent WITH ROLLUP
ORDER BY GROUPING(agent);

See the demo.