In a Virtual layer I'm trying to sum the values (real) on a table ('PESQUISA') but it returns NULL. What am I doing wrong?
This is the code I'm using:
select sum("renda_mensal") as sum
from PESQUISA
-
1Which data type is that? I am not sure if it is float– TarasMar 17, 2021 at 15:29
-
Its a real type. The layers is an csv file. I was reading about it, and it seems like it does not permit editions in the layer, is that correct? Anyway, I exported the table to a geopackage format and was able to perform the query! Thank you!– Felippe M.Mar 17, 2021 at 15:50
-
Can you provide a piece of your data ?– TarasMar 17, 2021 at 16:39
-
would be nice to have an explanation of your initial problem as @Taras said provide a subset of your data to check what's wrong ...– SnaileaterMar 17, 2021 at 17:17
-
Could there be a problem with one or more NULL values in your data table? I know this can interfere with the SUM function in some flavors of SQL but don't know the Qgis situation well enough. No doubt someone else will be able to confirm if this could be an issue.– Leigh BettenayMar 18, 2021 at 0:32
Add a comment
|
1 Answer
Let's assume there a point layer called 'random_points_test' with it's attribute table, see image below
where:
"Value_1"| Real(4,2)"Value_2"| Integer(3)"Value_3"| String(6)
When I apply the following SQL query for a 'Virtual Layer' through Layer > Add Layer > Add/Edit Virtual Layer...
SELECT SUM(p."Value_1") AS sum_1,
SUM(p."Value_2") AS sum_2,
SUM(p."Value_3") AS sum_3
FROM "random_points_test" AS p
However, appearing of NULL in the attribute table will not (shall not) affect the calculation, see images below.
Despite even a string data type, you can calculate the sum of values (real, integer, string) using the Virtual Layer (It seems to be really smart). So, I would claim that there is either something wrong with your data import or with values (the histogram of values may help here).
As a workaround I may refer you to one of my answers, see this.