How can I ensure that the SQL query will return a field containing integers and not text?
I am using a Spatialite DB for my QGIS project (QGIS 2.18). One of the task is to count the occurrences of values in an attribute (group), to join the resulting view table ("countvalues") to a spatial layer, and display the values based on the joined values.
Example of the grouping query in QGIS DB Browser:
CREATE VIEW countvalues AS SELECT "items"."group", COUNT("items"."group") AS "cnt" FROM "items" GROUP BY "items"."group"
However, the problem is that the type of the type of the newly created column ("cnt") is unknown. So, the symbology in QGIS will allow to symbolize the values as "Categorized" but not as "Graduated". (I assume this requires numbers)
I tried the follwing to cast the field values to integer numbers:
CREATE VIEW countvalues AS SELECT "items"."group", CAST(COUNT("items"."group") AS INTEGER) AS "cnt" FROM "items" GROUP BY "items"."group"
This did not work. If using REAL instead of INTEGER I can see that the values in the table contain a period. However, even then the data type of the "group" and "cnt" column remain empty in the field descripion of the DB Browser .