Building a query on mysql

Marcos Melgar 0
Viewed 32

I need to build a query to get all the account's monthly balances between Jan/2020 and Dec/2020. I dont know how to join all the tables that I need, but I understand that I need to sum the amount of the transfer_ins.amount + pix_movements.pix_amount where = pix_movements.in_or_out = "in". The result of each month I need to subtract with transfer_outs.amount + pix_movements.pix_amount where = pix.movements.in_or_out = "out" by account. The tables are in the image below. I think that I need to use the tables: transfer_ins transfer_outs accounts pix_movements d_time d_year

Basically I need this:

Account_id Year  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
---------  ----  --- --- --- --- --- --- --- --- --- --- --- ---
        1  2020   200  0   0   0   0   0   0   0   0 -500 400  0
        2  2020    0   0   0  500  0   0  900  0   0  100  0   0
        3  2009   100  0   0   0   0   0   0   0   0  200  500 0

Entity relationship model

mysql sql
1 Answers

It's hard to know what tables you're working with, so it's hard to give specific help.

It sounds like the shell of what you'll need is something like:

SELECT
    Account_id,
    YEAR(date_field),
    IF(MONTH(date_field) = 1, SUM(transfer_ins.amount) + SUM(pix_amount),0) as 'Jan',
    IF(MONTH(date_field) = 2, SUM(transfer_ins.amount) + SUM(pix_amount),0) as 'Feb',
    ...
FROM transfer_ins
JOIN pix_movements on pix_movements.date_field = transfer_ins.date_field

But we'd have a better sense of how to help if you let us know your table and column names, and which one(s) host the date you're using.

Alex Mills0
Related