Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

Example

share|improve this question
    
should be 3 (0+1+1+2+0) ?? really? – user7172 Mar 2 at 9:08

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:

Layer

So let's:

  1. 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:

    GroupStats plugin

  2. Save the results as a .csv file from the GroupStats toolbar:

    Export to csv

  3. 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)

  4. Do a Joins with the shapefile and the .csv file, matching the Sales fields:

    Joins

  5. The shapefile should now contain the sum values from the .csv file:

    Results

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.

share|improve this answer

If you update to the very latest QGIS version 2.14, you can use virtual layers making a sql statement like:

SELECT "MYTABLE"."MYID", Sum("MYTABLE"."SALES")  AS zip_sale /*:int*/
FROM "MYTABLE"
GROUP BY "MYTABLE"."ZIPCODE"

The UI looks like:

enter image description here

You can also use the DB manager, where you have a query builder behind the sql button:

enter image description here

The MYID column can be used to join the summed sales back to the main table. The join tab is under tabel properties.

Take a look at the QGIS visual changelog under Feature: Virtual layers

share|improve this answer

A way of doing this is the following:

  • In the attribute table, choose select by feature. Type "column-name" = 1023. All fields with the value 1023 are selected.
  • Go to vector tools --> Analysing tools --> basic statistics. Choose your layer and the column with the sales. Make sure to check the "selected values only" check-box. The sum will be displayed in the result window. You have to write it down ;)
  • repeat the steps for every zip-code
share|improve this answer
1  
P.S. Missing values are no problem. – Miron Mar 2 at 9:26
    
This is a simple and well-working approach. However it might be very time consuming when processing a large data set as Ammar mentioned... – dru87 Mar 2 at 9:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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