SQLite Forum

AVG with GROUP BY returns 0

AVG with GROUP BY returns 0

(1) By anonymous on 2021-04-19 07:45:22 [link] [source]

Hello guys!

I have a table 'meassurements' with 8mio rows. The rows have a 'block' column with is dividing the rows in blocks of 4000. I have 6 columns with decimal numbers and now want to get the average of one of those columns (val) per block

So I use

SELECT block, AVG(val) FROM meassurements GROUP BY block

but it always ends up as 0.

The values in the val column are between 0,04013 and 0,04288.

MIN/MAX seem to work wih GROUP BY but AVG is always returning 0. I am on 3.35.4 with SQLiteStudio 3.3.3 if that is of importance

Regards Sebastian

(2) By Keith Medcalf (kmedcalf) on 2021-04-19 08:35:06 in reply to 1 [link] [source]

Define "decimal numbers". I suspect that you definition is "text that looks like a number to me" but is not actually a number.

(3) By Keith Medcalf (kmedcalf) on 2021-04-19 08:39:17 in reply to 1 [link] [source]


select block, avg(cast(replace(val, ',', '.') as real)) from meassurements group by block;

(4) By anonymous on 2021-04-19 08:43:51 in reply to 3 [link] [source]

OP here. This seems to work!

I created the columns as DECIMAL(4, 9) but having a second look at it it seems like it got stored as text or something.

How did that happen?

(5) By anonymous on 2021-04-19 08:45:48 in reply to 2 [link] [source]

OP once again. Should I do an UPDATE on the "DECIMAL" columns and swap , for . or is there some different conversion to that?

I thought the import would take care of this

(6) By Warren Young (wyoung) on 2021-04-19 10:59:28 in reply to 4 [link] [source]

SQLite doesn't have a specific DECIMAL(x, y) data type. It has a fallback for SQL compatibility with other RDBMSes, but it probably doesn't give you the behavior you expect.

You should probably switch to the decimal extension.

(7) By Rado (antlor) on 2021-04-19 11:35:01 in reply to 5 [source]

Yes you can update column but like this:

update meassurements
set val = cast(replace(val, ',', '.') as real)

Cast will change val column type text to real so now you can do:

select block, avg(val) 
from meassurements 
group by block

(8) By Keith Medcalf (kmedcalf) on 2021-04-19 18:45:59 in reply to 5 [link] [source]

import as in import using the shell .import command?

import into a "new" table will import all data values as text. Only when importing into an already existing table are the values stored with column affinity applied.

Other import mechanisms will have their own limitations defined by that mechanisms import semantics. (And I have no idea how SQLiteStudio imports data as this is an application program concern.)