How to display an accumulated key=value taken from variable key columns?

Question

I have a MySQL transfers table with the following structure:

CREATE TABLE `transfers` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `id_account_destination` bigint(20) unsigned NOT NULL,
 `id_account_origin` bigint(20) unsigned NOT NULL,
 `amount` decimal(10, 2) signed NOT NULL DEFAULT 0,
 `created_at` datetime DEFAULT CURRENT_TIMESTAMP,

 PRIMARY KEY (`id`),
 KEY `id_account_destination` (`id_account_destination`),
 KEY `id_account_origin` (`id_account_origin`),
 KEY `created_at` (`created_at`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table stores transfers, obviously. It records the transfer amount and the two accounts involved: the origin and the destination.

What I need is to create a SELECT statement that takes all the given transfers from a period (filtered by created_at) and shows me how much income and outcome do all the present accounts have in that segment. Something like:

+------------+---------------+-----------------|
| id_account | income_amount | outcome_ammount |
+------------+---------------+-----------------|
|         19 |     27690.87  |        57204.80 |
|        112 |      1000.00  |         2349.00 |
|       1011 |    575877.56  |        17454.50 |
|         17 |    135002.61  |          204.30 |
+------------+---------------+-----------------|

The difficulty here is that the id_account column will take a id_account_origin or id_account_destination from any transfer and find all its other occurrences through the rest of the transfers. If the account is found in the "origin" position, the transfer's amount will be added to the outcome_ammount column; but if the account is found in the "destination" position, the transfer's amount should instead be added to the income_ammount column.

So the output table is completely different from the transfers table, although all the needed information is already there.

What I've acchieved so far was to get that information only in one way:

SELECT
    id_account_origin,
    SUM(t.amount) AS outcome_amount
FROM transfers t
GROUP BY t.id_account_origin;

Which returns:

+-------------------+----------------+
| id_account_origin | outcome_amount |
+-------------------+----------------+
|             10009 |     2761390.87 |
|             10012 |        1000.00 |
|             10011 |      575877.56 |
|             10007 |      135002.61 |
+-------------------+----------------+

And is far easier than crossing columns as expected above.

GMB · Answer 1 · 2023-05-04T20:01:15.590

1

One approach unpivots the data, then aggregates.

In recent MySQL versions, we can use a lateral join, which avoids scanning the table twice with union all:

select x.id_account, 
    sum(x.income_amount) income_amount, 
    sum(x.outcome_amount) outcome_amount
from transfers t
cross join lateral (
    select t.id_account_destination, t.amount, 0 
    union all select t.id_account_origin, 0, t.amount
) x(id_account, income_amount, outcome_amount)
group by x.id_account

Reference: MySQL - How to unpivot columns to rows?

Here is a small demo.

Sample data:

id	id_account_destination	id_account_origin	amount	created_at
1	1	2	100.00	2023-05-04 19:59:37
2	1	3	50.00	2023-05-04 19:59:37
3	4	1	30.00	2023-05-04 19:59:37

Results:

id_account	income_amount	outcome_amount
1	150.00	30.00
2	0.00	100.00
3	0.00	50.00
4	30.00	0.00

edited May 04 '23 at 20:01

answered Apr 28 '23 at 21:27

GMB

216,147
25
84
135

Although I see the benefit of the lateral join, the `values` statement here only returns a table with the **first** case of the transfers table; it doesn't return two rows for **each** row in `t` but only for the first one. It seems like the select statement stops on the first row of `t`. How can I get it to continue producing output for each `t` row, joining with the corresponding `values` derived table for that row? – Sebastián Sameghini May 03 '23 at 23:00
@SebastiánSameghini: ah, it seems like the `values` row constructor is yet not stable enough in MySQL. We can use regular `select`s instead in the lateral join. I updated the query in my answer and added a fiddle for your reference (this is MySQL 8.0.30). – GMB May 04 '23 at 20:03

score 1 · Answer 2 · answered Apr 28 '23 at 21:28

Write another query for id_account_destination. Then combine them with UNION to unpivot.

SELECT id_account, SUM(outcome_amount) AS outcome_amount, SUM(income_amount) AS income_amount
FROM (
    SELECT id_account_origin AS id_account, SUM(t.amount) AS outcome_amount, 0 AS income_amount
    FROM transfers t
    GROUP BY id_account
    UNION ALL
    SELECT id_account_destination AS id_account, 0 AS outcome_amount, SUM(-t.amount) AS income_amount
    FROM transfers t
    GROUP BY id_account
) AS x 
GROUP BY id_account

How to display an accumulated key=value taken from variable key columns?

2 Answers2