4

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.

Improve this question
8
  • 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?
    – Erik
    Nov 20, 2019 at 11:02
  • @Erik I have edited my question. It now gives all sites the same value...
    – Janine McMahon
    Nov 20, 2019 at 12:05
  • 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.
    – Erik
    Nov 20, 2019 at 12:11
  • 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.
    – Janine McMahon
    Nov 20, 2019 at 12:21
  • 1
    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.
    – Erik
    Nov 20, 2019 at 12:43

1 Answer 1

Reset to default
6

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" )

Improve this answer
6
  • It can be so easy.
    – Erik
    Nov 20, 2019 at 12:51
  • sometimes :-). But it is really hard to understand all the possibilities of the aggregate-functions...
    – PieterB
    Nov 20, 2019 at 12:52
  • ok, thank you, I will give it a go!
    – Janine McMahon
    Nov 20, 2019 at 12:55
  • It worked. Thank you!!
    – Janine McMahon
    Nov 20, 2019 at 13:05
  • I edited my answer with more info about child and parent
    – PieterB
    Nov 20, 2019 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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