0
I’m trying to make a pick up the orders totals grouped by date as follows
SELECT
sum(pit.preco) AS preco,
sum(pit.quantidade) AS quantidade
FROM cliente c
LEFT JOIN erp_pedido ped ON ped.id_cliente = c.id_cliente
LEFT JOIN erp_pedido_it pit ON pit.id_pedido = ped.id_pedido
WHERE ped.tipo = 'C'
AND ped.id_cliente = 2
GROUP BY
ped.data_inicio ,
c.nome,
ped.id_cliente
ORDER BY ped.data_inicio desc,
ped.id_cliente
This results in
2017-03-01 00:00:00 ; CLIENT NAME ; 2 ; 1.5000 ; 209.0000
2017-02-01 00:00:00 ; CLIENT NAME ; 2 ; 3.0000 ; 418.0000
2017-01-01 00:00:00 ; CLIENT NAME ; 2 ; 4.5000 ; 627.0000
2016-12-01 00:00:00 ; CLIENT NAME ; 2 ; 6.0000 ; 836.0000
2016-11-01 00:00:00 ; CLIENT NAME ; 2 ; 7.5000 ; 1045.0000
2016-10-01 00:00:00 ; CLIENT NAME ; 2 ; 9.0000 ; 1254.0000
2016-09-01 00:00:00 ; CLIENT NAME ; 2 ; 10.5000 ; 1463.0000
2016-08-01 00:00:00 ; CLIENT NAME ; 2 ; 12.0000 ; 1672.0000
2016-07-01 00:00:00 ; CLIENT NAME ; 2 ; 13.5000 ; 1881.0000
2016-06-01 00:00:00 ; CLIENT NAME ; 2 ; 15.0000 ; 2090.0000
2016-05-01 00:00:00 ; CLIENT NAME ; 2 ; 16.5000 ; 2299.0000
I hoped that it would add up separately for each date and not add up everything sequentially, it should look like this:
PRECO ; QUANTIDADE
1.5000 ; 209.0000
1.5000 ; 209.0000
1.5000 ; 209.0000
1.5000 ; 209.0000
1.5000 ; 209.0000
1.5000 ; 209.0000
What I’m doing wrong?
The result you posted was not generated by this select?
– Reginaldo Rigo
Leave it at that: GROUP BY Ped.data_start
– Reginaldo Rigo
There’s something wrong with your post, "It results in" seems to be switched with "it should look like this' but @Reginaldo Rigo’s solution seems to be the right one
– Motta
@Reginaldorigo is the same sql I just removed the columns that are not relevant from the select I posted, I can not leave only the Ped.data_start Postgre requires the other fields in the group by
– Jonathan dos Santos
That is why the same SQL is important, because otherwise we are talking about what SELECT we are seeing and you are talking about the other SELECT you are seeing. But anyway, if you put the fields C.name, Ped.id_client in SELECT then the bank will even require you to group through these fields as well and then you will not get the result you expect. You need to remove these fields from SELECT in order to group only by DATA_INICIO.
– Reginaldo Rigo