3
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 |
+------------+----------+--------+----------+--------------+
The bond of
id_balanco
is already being done withUSING
– Roberto de Campos
@Robertodecampos complicating the
query
and performing theSUM
insubqueries
, which is very heavy. The ideal would be to use this way as shown. If you put this in an execution plan you will notice that it is much more performative– Sorack
I agree, just commented that the filter for
id_balanco
was already being done, even has my +1– Roberto de Campos
Thank you both so much for the strength. Looking at the answers of both I could see what I was getting wrong.
– Rafael S.