Incomplete sum with JOINS in Mysql

Question

I’ve had a doubt for a few days and I can’t find a solution.

I own the tables:

swing

+------------+---------+------------+----------+
| id_balanco | id_loja |    data    | dinheiro |
+------------+---------+------------+----------+
|          1 |       1 | 2019-01-01 | 100.00   |
|          2 |       1 | 2019-01-02 | 25.00    |
|          3 |       1 | 2019-02-01 | 50.00    |
|          4 |       1 | 2019-02-02 | 50.00    |
+------------+---------+------------+----------+

balance cards

+----+------------+-----------+--------+
| id | id_balanco | id_cartao | valor  |
+----+------------+-----------+--------+
|  1 |          1 |         2 | 100.00 |
|  2 |          2 |         1 | 50.00  |
|  3 |          3 |         3 | 50.00  |
|  4 |          4 |         3 | 50.00  |
+----+------------+-----------+--------+

balancing

+----+------------+-----------+--------+
| id | id_balanco | descricao | valor  |
+----+------------+-----------+--------+
|  1 |          1 | Água      | 100.00 |
|  2 |          2 | Luz       | 50.00  |
|  3 |          3 | Diversos  | 50.00  |
|  4 |          4 | Diversos  | 50.00  |
+----+------------+-----------+--------+

balanco_suppliers

+----+------------+-----------+--------+
| id | id_balanco | id_cartao | valor  |
+----+------------+-----------+--------+
|  1 |          1 |         1 | 100.00 |
|  2 |          2 |         4 | 50.00  |
|  3 |          3 |         3 | 50.00  |
|  4 |          4 |         3 | 50.00  |
+----+------------+-----------+--------+

With these tables I need to make a query with the totals of each month of a store and for all stores (ok, here I believe it is just to remove the id of where). What I’ve been trying is this:

SELECT data, SUM(dinheiro) AS dinheiro, cartao, despesas, fornecedores
FROM balanco b
    LEFT JOIN ( SELECT id_balanco, SUM( valor ) AS cartao FROM balanco_cartoes GROUP BY id_balanco ) BC USING( id_balanco )
    LEFT JOIN ( SELECT id_balanco, SUM( valor ) AS despesas FROM balanco_despesas GROUP BY id_balanco ) BD USING( id_balanco )
    LEFT JOIN ( SELECT id_balanco, SUM( valor ) AS fornecedores FROM balanco_fornecedores GROUP BY id_balanco ) BF USING( id_balanco )
WHERE id_loja = ? GROUP BY YEAR(data), MONTH(data) ORDER BY YEAR(data) DESC, MONTH(data) DESC

The problem is that this query sums right only to column dinheiro that is in the first table, in the other tables it only takes the first line, without sum.

The result I’m getting:

+------------+----------+--------+----------+--------------+
|    data    | dinheiro | cartao | despesas | fornecedores |
+------------+----------+--------+----------+--------------+
| 2019-02-01 | 100.00   | 50.00  | 50.00    | 50.00        |
| 2019-01-01 | 125.00   | 100.00 | 100.00   | 100.00       |
+------------+----------+--------+----------+--------------+

What I’d like to get:

+------------+----------+--------+----------+--------------+
|    data    | dinheiro | cartao | despesas | fornecedores |
+------------+----------+--------+----------+--------------+
| 2019-02-01 | 100.00   | 100.00 | 100.00   | 100.00       |
| 2019-01-01 | 125.00   | 150.00 | 150.00   | 150.00       |
+------------+----------+--------+----------+--------------+

Answer 1

Just link the tables by id_balanco and use a GROUP BY for the month and year using the SUM to sum the values of each table:

SELECT DATE_FORMAT(b.data,'%m-%Y') AS mes,
       SUM(b.dinheiro) AS dinheiro,
       SUM(bc.valor) AS cartao,
       SUM(bd.valor) AS despesas,
       SUM(bd.valor) AS fornecedores
  FROM balanco b
  LEFT JOIN balanco_cartoes bc ON bc.id_balanco = b.id_balanco
  LEFT JOIN balanco_despesas bd ON bd.id_balanco = b.id_balanco
  LEFT JOIN balanco_fornecedores bf ON bf.id_balanco = b.id_balanco
  GROUP BY DATE_FORMAT(b.data,'%m-%Y')

See working on DB Fiddle