I would like to calculate the sum of values in one column, based on values in another column. To illustrate my question I uploaded the picture below. The second column consists of zipcodes, the third total sales. I would like to know what the total sales per zipcode are? So in this case:
What is the sum of column three for column two's value 1023
? Output then should be 4 (0+1+1+2+0)
. The sum for 1024
then should be 11,5 following the same logic.
As this is quite a large dataset I would like to have the QGIS field calculator (or any other tool) calculate a new field which states the total sales in the zipcode. So far I can't seem to get it working and was hoping someone here could help me.
As you can see I have some missing values, is this a problem in calculation? Perhaps good to mention is that it is a table.
Best Answer
Update: QGIS 2.18 (07/07/2017):
In the Field Calculator, you can use the Aggregate function
sum
which allows you to sum the values of a column based on an expression. You can also, optionally, group and filter features for summation. So in your field calculator, you could simply use:Which does exactly the same as the method using the GroupStats plugin.
Original post: 02/03/2016
Use the GroupStats plugin which you may need to download from Plugins > Manage and Install Plugins.
Here's an example layer using some of the attributes you have shown:
So let's:
Run the GroupStats plugin, select the relevant fields for summing (
Sales
for rows;Sum
for values) and then click Calculate. You should see the results showing the sum of each sale:Save the results as a .csv file from the GroupStats toolbar:
Import the .csv file back into QGIS either by dragging it to the interface or from the toolbar:
Layer > Add Layer > Add Delimited Text Layer (note that the values are separated by semi-colons)
Do a Joins with the shapefile and the .csv file, matching the
Sales
fields:The shapefile should now contain the sum values from the .csv file:
You could, if you wish, proceed to use the
Save As...
option on the shapefile and save it as a new one to keep the joined fields.