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: https://dbfiddle.uk/?rdbms=sqlite_3.27 — forpas, Jan 20 '21 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, Jan 20 '21 at 17:13
With your first query, what does adding `typeof(pg.valor)` to the result columns give you? — Shawn, Jan 20 '21 at 17:37
Those look good. Yeah, set up a fiddle with sample data that reproduces this if you can; I don't see anything obvious especially given your edit. — Shawn, Jan 20 '21 at 17:45
Tks for you tips, I solve with your help. Forcing the valor field as Real. select descricao, sum(cast(pg.valor as Real) ) as valor. — Márcio Rossato, Jan 20 '21 at 17:51
But, do you know why SQLite considers my field as an integer if on the table it is NUMERIC ? — Márcio Rossato, Jan 20 '21 at 17:52
That's what numeric affinity does. See https://www.sqlite.org/datatype3.html#type_affinity — Shawn, Jan 20 '21 at 18:28
It is not the column, but the value of that column that is considered as integer: https://sqlite.org/lang_corefunc.html#typeof — forpas, Jan 20 '21 at 18:54
I will change the type from NUMERIC to Real on tables to avoid this problem, if it works I'll post it here, — Márcio Rossato, Jan 20 '21 at 19:15

Márcio Rossato · Answer 1 · 2021-01-21T21:56:59.910

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 '21 at 21:56

answered Jan 21 '21 at 13:59

Márcio Rossato

971
1
11
20

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 Jan 21 '21 at 16:36
Check this: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=00f38dd7814bb69bc6b7c5388a717376 – forpas Jan 21 '21 at 16:49
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 Jan 21 '21 at 20:15
Anyway, how I can't check if the problem is the DLL, I edited the answer. tks – Márcio Rossato Jan 21 '21 at 21:44
Also, using the fiddle you created, I changed to real type, and the typeof on all rows are the same (real) https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=9a6201913b82a7ce91ed05e6166d3982 – Márcio Rossato Jan 21 '21 at 21:52
In my 1st comment under your question I said: *It is wrong to use , as the decimal separator for numeric values* and you replied: *the value in database was correct as float*. So, is it maybe that the problem is the app you use to fetch the results of your query? Execute the sql statements directly on your database by using a tool like DB Browser for SQLite, with the latest version of SQLite. If you get correct results then you know that the problem is your app. If the results are wrong (which I doubt) then the problem I suspect is with your data. – forpas Jan 21 '21 at 21:52
SQLite uses a dynamic type system, so the data type of the columns is not important here. You can get correct results even if the data type of the columns is TEXT: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a4574b4b0477eb09c3fd19f0a012329d – forpas Jan 21 '21 at 21:54
1

You are right, I believe the problem really is my app. tks – Márcio Rossato Jan 21 '21 at 21:54
I edited the question too, to explain the tools I'm using. – Márcio Rossato Jan 21 '21 at 21:56
1

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. – forpas Jan 21 '21 at 22:06

SQLITE query GROUP BY with SUM Using Delphi and Zeos

1 Answers1