Combining rows in attribute tables in QGIS 3.4 with some strings that should be added together and others left untouched

Question

I want to do the following with some fields in the attribute table but I have no idea how to do it: Each row is about a parcel and its selling price, it also contains its address, cadastral ID etc. The problem is that sometimes, one sale included several parcels, in this case the total price of the sale is included in each parcel row. As I would like to get the price/sqm of the land sale, it obviously gets some wrong result since the price is for all the parcels in one sale, not each individual parcel.

For instance, some land was sold for 1000 euros (just for the sake of explanation). The land is located 1, Olive Street. This land includes 3 parcels A, B and C with 300, 200 and 100 sqm respectively for a total of 600 sqm. In this case, the table of attributes look like this:

Parcel_A | 300 | 1000 | 1 | Olive Street

Parcel_B | 200 | 1000 | 1 | Olive Street

Parcel_C | 100 | 1000 | 1 | Olive Street

Right now if I just do 1000/300 = 3,3 euros/sqm this is of course wrong since 1000 euros is the total price of this piece of land which includes parcels A, B and C.

I would like to transform the previous table and arrive at the following result :

Parcel_A_Parcel_B_Parcel_C | 600 | 1000 | 1 | Olive Street | 1,66 (1,66 being the price/sqm of the total piece of land).

I guess I have to do something like this (just in terms of logic) : if the address (street name and street number) is the same, concatenate Parcel Name field, add area, keep just one instance (any instance since they will be similar) of area, street name and street number. And then (ideally), replace the original rows by the resulting row (since I won't need the "old" rows anymore). Also, in case a piece of land only includes one parcel, it should of course keep it in place since there is no issue in this case.

How do I use the field calculator to perform this kind of thing (if it is even possible)?

Answer 1

You can achieve this using a virtual layer.

It would group the records by civic number + street name (though if you can group by ID do so), union the geometries, sum the area and do the division using this sum.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Feel free to add more fields from your layer, and to adjust the names!

SELECT group_concat(name,'_') as new_name,
       st_union(geometry) as geometry, 
       sum(parcel_area) tot_area, 
       min(total_price) as total_price, 
       1.0*min(total_price)/sum(parcel_area) as price_per_area_unit,
       civic_number,
       street_name
FROM myLayer
GROUP BY civic_number, street_name