1

I have a table without geometry that contains three columns: Item, Sub-item, and amount. I would like to create a new table with the sum of the amount of the sub-items that have the same Item attribute. (These categories are invented)

Normally I would use the GDAL Dissolve with the Item as the dissolve field. But in QGIS it doesn't accept layers without geometry as an input.

I have created dummy point geometry to get around this but feel like I must be missing something. Could I just be using an sql query? Also I would like it if any of the sub-items are null it would give me a null sum. Currently it only gives a null if all of the items are null.

4
  • you can use a virtual layer to run an sql query. Note that sum() ignores null except if ALL values are null
    – JGH
    May 21 '20 at 20:48
  • What do you mean by a 'table'? May 21 '20 at 21:30
  • Tabular data in a geopackage
    – Baswein
    May 21 '20 at 21:57
  • First I've heard of that, but try the virtual layer sql query mentioned above. You're not dissolving geometry, you're grouping tabular data. May 21 '20 at 23:20
4

To answer one of your needs, try using the Aggregate processing tool. You can select one column to group by and use a GUI to determine what aggregate functions to use, renaming columns and adding as many new aggregates as you like.

enter image description here

However it won't discard features or return NULL if a null value is found. You would need an SQL query via Virtual Layers for that.


If you want it to return a NULL for sum(amount) if your subitem column is null, you could try the following

SELECT 
item, 
group_concat(subitem) AS subitems, 
CASE WHEN 
    group_concat(coalesce(subitem,'NULLPLACEHOLDER')) LIKE '%NULLPLACEHOLDER%' THEN NULL
    ELSE sum(amount) 
    END AS sum 
FROM table_name
GROUP BY item

enter image description here

If you want it to return a NULL for sum(amount) if the amount column is null:

SELECT 
item, 
group_concat(subitem) AS subitems, 
CASE WHEN 
    group_concat(coalesce(amount,'NULLPLACEHOLDER')) LIKE '%NULLPLACEHOLDER%' THEN NULL
    ELSE sum(amount) 
    END AS sum 
FROM table_name
GROUP BY item

enter image description here

11
  • Interesting query.. however it works only if the 1st item in the list is Null. Else, it would be a regular sum() that ignores the null. It is relying on the way SqlLite allows using the 1st row value during an aggregation (which is forbidden in postgres) to decide on whether a Null or an aggregation will occur.
    – JGH
    May 22 '20 at 11:41
  • 1
    Good catch, I did think it was unusual behaviour and thought I got lucky. I've updated the query with an awkward alternative.
    – she_weeds
    May 22 '20 at 13:02
  • The Aggregate tool is great! Somehow I didn't know about it. Trying the virtual layers now. Do I need to replace nullplacehloder with something?
    – Baswein
    May 22 '20 at 15:06
  • 1
    No you can use whatever you want for the text. Just make sure it's the same throughout the script. It basically concatenates all your values, with any null values replaced by a placeholder text, so that if the placeholder text is found in any grouping the result can be made null.
    – she_weeds
    May 22 '20 at 16:05
  • 1
    I was able to load it using Virtual Layer, but make sure you select No Geometry. I also didn't specify an unique feature identifier. I'm using QGIS 3.12
    – she_weeds
    May 22 '20 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.