Question 88673 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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_ammount) outcome_amount
from transfers t
cross join lateral (
    values row(t.id_acount_destination, t.amount, 0), 
           row(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?

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search