SQLITE query GROUP BY with SUM Using Delphi and Zeos

Question

I have an app created using Delphi XE3 accessing the SQLite database with Zeos Component.

A select command in SQLite that return 3 rows:

 select p.descricao, pg.valor
from pagamento pg
inner join venda v on pg.origem = 'venda' and v.id_venda = pg.id_origem
left join pagamento_tipo p on pg.id_pagamento_tipo = p.id_pagamento_tipo
where v.data >= '2021-01-19' and v.data <= '2021-01-19'
and v.ra_status_venda in ( 'Finalizado', 'Pedido')

but when I put the group command the information result is wrong.

    select p.descricao, sum(pg.valor) as valor
from pagamento pg
inner join venda v on pg.origem = 'venda' and v.id_venda = pg.id_origem
left join pagamento_tipo p on pg.id_pagamento_tipo = p.id_pagamento_tipo
where v.data >= '2021-01-19' and v.data <= '2021-01-19'
and v.ra_status_venda in ( 'Finalizado', 'Pedido')
group by descricao

the P02 not sum.

and, if I sum all row, without group by, the value was correct too.

select sum(pg.valor) as valor
from pagamento pg
inner join venda v on pg.origem = 'venda' and v.id_venda = pg.id_origem
left join pagamento_tipo p on pg.id_pagamento_tipo = p.id_pagamento_tipo
where v.data >= '2021-01-19' and v.data <= '2021-01-19'
and v.ra_status_venda in ( 'Finalizado', 'Pedido')

PS: in another period this commands sum correctly. PS2 I'm using dll 32bits for windows.

It is wrong to use , as the decimal separator for numeric values. You should use .. In any case you should not get 0 as result, but 39. Can you reproduce the issue in a fiddle: dbfiddle.uk/?rdbms=sqlite_3.27 — forpas
– forpas, Commented Jan 20, 2021 at 17:09
Tks for you response.The "," are used to show decimal separator in my country, but the value in database was correct as float. I'll try to put on fiddle. — Márcio Rossato
– Márcio Rossato, Commented Jan 20, 2021 at 17:13
With your first query, what does adding typeof(pg.valor) to the result columns give you? — Shawn
– Shawn, Commented Jan 20, 2021 at 17:37

Márcio Rossato · Accepted Answer · 2021-01-21 21:56:59Z

1

With tips @Shawn and @forpas I solve this problem, the SQLite type of field NUMERIC converts the numbers to other types in the RUNTIME select command, how the two first values were INTEGER and the last value were Real, apparently my program not recognize two different types in the same column.

To solve I change the type of field in the database from NUMERIC to REAL.

edited Jan 21, 2021 at 21:56

answered Jan 21, 2021 at 13:59

Márcio Rossato

1,1491 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

forpas Over a year ago

SQLite does not convert numbers to other types unless you explicitly apply this conversion by code. Also SQLite sums integer and real values perfectly fine. The issue that you described in your question is not reproducible so we don't know the source of the problem.

forpas Over a year ago

Check this: dbfiddle.uk/…

Márcio Rossato Over a year ago

I'm using win 32 dll, and the result is different than fiddle. I updated the dll to the most recent version before open this question.

Márcio Rossato Over a year ago

You are right, I believe the problem really is my app. tks

forpas Over a year ago

I'm not familiar with Delphi or Zeos, but it would help to post the app's code that returns these results, so maybe someone may have a solution for you.

|

Collectives™ on Stack Overflow

SQLITE query GROUP BY with SUM Using Delphi and Zeos

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related