Adding field in table counting from different table using QGIS

Question

I just started to work with QGIS recently and am struggling with the following:

• I have 1 a CSV table on offshore branches of certain institutions including info on their respective countries of origin. No geographical data except the columns with country names (Home Country, Host Country).

  Here is an example of the table with the relevant columns:

Institution name	Institution Branch	Home Country	Host Country
xxx	xxx2	Germany	Saudi Arabia
yyy	yyy2	Germany	India
zzz	zzz2	USA	India

• I also have 2 a polygon layer with the world countries in which each country takes 1 row, and each with it's own polygon represented in the shapefile (the country name column is named name)

Both attribute tables use the same format for the country names.

Now I would like to

1. add a field (column) to the 2 country layer's attribute table that counts for each country how many institutions have it as a Home Country in the CSV table. Using the above table as an example, the new column would count 2 for Germany, and 1 for USA.
2. add additional columns to the 2 country layer's attribute table that count for each country how many institutions have it as a Home Country in the CSV table AND have a branch in a certain Host Country. For example in the above table I would like to only count for those countries with institutions with a branch in Host Country = India. The resulting column should count 1 for Germany, and 1 for USA.

Answer 1

One step for me :

• Select the country layer 2, open field calculator, name your new field of type Integer and compute it with the expression :

coalesce(
  aggregate(
    layer:= 'offshore_institutions_layer_name',
    aggregate:= 'sum',
    expression:= 1,
    filter:= "Home Country" = attribute(@parent, 'name')
  )
  , 0)

Explanation :

This expression aggregate the 1 offshore institutions with the same "Home Country" name as the current country layer 2 feature "Home Country" value, and sum the value 1 for each (it's the same as count in this case).

For the second part, you can add a condition to your filter :

filter:= "Home Country" = attribute(@parent, 'name') AND "Host Country" = 'India'