Calculating the sum of a column based on another column in another table in the field calculator of QGIS

Question

I have a records table with site numbers and abundances of different species at that site (e.g. site 1 has 6 common bats and 13 pipistrellus bats).

enter image description here

I would like to sum total number (i.e. 6+13=19) at that site into another column within another table of a point shapefile locating the sites in some kind of expression used in the field calculator. Is this possible?

I have tried

aggregate( 'records', 'sum', "Abundance", "SiteNum") 

but this has given me the same value (808) for every site in the new column of the point shapefile attribute table i.e. is ignoring the factor of site number.

• Erik: Could you provide example data from the attribute table, as well as describe more detailed the feature types of records and sites? Also, have you had a look at the aggregate-expression of the field calculator?
• Janine McMahon: @Erik I have edited my question. It now gives all sites the same value...
• Erik: I guess the sum part should read something like ...'sum("Abundance",group_by:="SiteNum")'... - but to be honest I have no idea what the aggregate- expression should read then. Aggregate is like dark humor - not everyone gets it.
• Janine McMahon: The field calculator doesn't seem to accept that string. Anything I have tried just gives all the sites the same value and ignore the factor.
• Erik: Ok, the problem is grabbing the created info to put it into the site-layer. Within the abundance-layer sum(Abundance,SiteNum) should work fine. So, what happens if we go with aggregate('layernamefromthefieldcalc','max','sum(Abundance,SiteNum)')? It is important that you use the layername with which the abundance-layer is referenced under in the field calculator (point "map layer") - the simple 'records' wont work most likely.

Answer 1

So you want to sum the "Abundance" grouped by "SiteNum" and place the results in another_table with a common ID?

The clue is in relation_aggregate(). More info: https://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/expression.html#aggregates-functions and in the expression-builder

First, you have to define a relation: Project> properties> relations.

• Set the relation with 'records' as child and 'another_layer' as parent.

• Give the relation a name. This name you need in the relation_aggregate()

  relation_aggregate( relation:= 'name', aggregate:= 'sum', expression:= "Abundance" )

• Erik: It can be so easy.
• PieterB: sometimes :-). But it is really hard to understand all the possibilities of the aggregate-functions...
• PieterB: I edited my answer with more info about child and parent
• PieterB: Which file-format is used to store the records? Which are the fieldproperties of 'SiteNum'? Are there unexpected spaces in the data? The same questions has to be asked for the other layer.