2
Guys I’m having a question, follow the SELECT down below:
SELECT
o.matricula,
v.cd_operador,
o.hora,
c.nm_nome,
COALESCE(SUM(o.quantidade), 0) AS sacolax,
v.sas03 AS sas03,
COALESCE(SUM(o.quantidade), 0) - v.sas03 divergencia
FROM sacolax_tb_operacao o
LEFT JOIN (
SELECT o.cd_matricula,
o.cd_operador,
COALESCE(SUM(v.qtd), 0) AS sas03
FROM sacolax_tb_venda v
LEFT JOIN sacolax_tb_operador o ON v.cd_operador = o.cd_operador
WHERE dt_venda = '2020-05-14'
GROUP BY cd_matricula ) v ON v.cd_matricula = o.matricula
LEFT JOIN tb_colaboradores c ON o.matricula = c.cd_matricula
WHERE DATE(hora) = '2020-05-14'
GROUP BY o.matricula
ORDER BY hora DESC;
As a result:
matricula cd_operador hora nm_nome sacolax sas03 divergencia
295888 5888 2020-05-14 16:00:36 OPERADOR1 49 49 0
106328 6328 2020-05-14 15:54:47 OPERADOR2 47 47 0
how do I get back more days without having sum between days in the columns sacolax and sas03? How below:
matricula cd_operador hora nm_nome sacolax sas03 divergencia
295888 5888 2020-05-11 16:00:36 OPERADOR1 20 20 0
295888 5888 2020-05-14 16:00:36 OPERADOR1 49 49 0
106328 6328 2020-05-14 15:54:47 OPERADOR2 47 47 0
Note: the code today works with only one day in case you seek more than one day with the BETWEEN it adds the results of the columns sacolax and sas03
Hello! To improve the question, I suggest describing the difference between the first and second result. Otherwise, whoever reads the question will need to figure out for themselves what is different between the 2, which makes it difficult to understand and more difficult to get the answer you want. The title of the question can be improved too, it was well "generic".
– Dherik
Maybe he added the sacolax results because there is one
COALESCE(SUM(o.quantidade), 0) AS sacolax,?– anonimo
The date of "200-05-11" does not meet the WHERE "2020-05-14"
– Motta
So the sum of the sacolax is necessary, what I don’t want is that if you add up the days, with the test that I did with BETWEEN it added up the two lines:
295888 5888 2020-05-11 16:00:36 OPERADOR1 20 20 0
295888 5888 2020-05-14 16:00:36 OPERADOR1 49 49 0and played with the latest date 2020-05-14, as follows:295888 5888 2020-05-14 16:00:36 OPERADOR1 69 69 0– Cris e Rafa Gonçalves
I don’t understand, I’m sorry.
– Motta
If I understand, perhaps you should add the.hora field to your GROUP BY clause.
– anonimo
when I include.hora in GROUP BY it returns:

210010 2100 2020-05-14 07:01:07 operador1 2355 63 2292– Cris e Rafa Gonçalves
Amigo, Tried to put group by on date? So it groups by date. At the end, you can put like this: GROUP BY o.matricula , dt_venda ORDER BY hora DESC;
– Douglas Marques