5

I'm using QGIS for some analysis. I have data in this format: Name is a text variable and categories are all integers enter image description here

And I want to generate a sum for all categories for each name, ie a table like this: enter image description here

Is this possible is QGIS. I've been using the Group Stats plugin and I'm able to get the sums one category at a time but my data actually has many more categories than I've displayed here so it would take me a very long time to do.

I've been putting Name into the row box in Group Stats and in the value box I put Sum and CategoryA.

I'm quite new to QGIS so any help you could give would be great!

  • Something seems off here - you should be able to do all at once otherwise it'd be a pretty limited tool. I don't have it installed to test, but what if you switch your inputs. Have name as columns, and the categories as rows. Or is it sum and all categories into value box, and names into rows. This is basically a Pivot Table function, and I've not worked with that interface much. – Chris W Jul 23 '15 at 4:11
5

Before I get to how to in QGIS I feel I should mention that from table browser in QGIS you can select and copy the whole table and paste into your favorite spreadsheet software and calculate sums (or other) there.

If you really want to do it in QGIS, the best way is to use SQL query for this. Meaning you need to work in some database (don't panic, it is pretty simple). I guess you don't have one yet so there are some steps to take beforehand:

  1. In QGIS in Browser (dockable window) is Spatialite entry, right click and create database
  2. now run DB Manager, under SpatiaLite find your fresh database name, click on plus next to it which should connect this database
  3. in top bar click down arrow for import, select your input file and write output table name (table_name in later SQL)
  4. in top bar click refresh and next to it click on SQL window icon

Once you have your table in database write your SQL query and click Execute:

Select Name, Sum(CategoryA) AS SumCatA, Sum(CategoryB) AS SumCatB, Sum(CategoryC) AS SumCatC FROM table_name GROUP BY Name

This will give you the table you asked for which you can select and copy to spreadsheet.

In my logic Group Stats should allow following:

  1. Add Name to "Rows"
  2. Add CategoryA, CategoryB, CategoryC into columns
  3. Add sum to Value
  4. Calculate

So exactly as you have it in your second table. But that doesn't work. Calculate button is not available with this settings. All I was able to do is calculate sum for only one column at a time:

  1. Add Name to "Rows"
  2. Add CategoryA into Value
  3. Add sum to Column (no problem to add also count, median etc. as columns)
  4. Calculate

You have sum for CategoryA. After that remove CategoryA from Value and put CategoryB instead. At the end you probably end up merging the sum columns in some spreadsheet software so I guess it is better to do the whole thing in (free/opensource) spreadsheet software anyway.

  • This was really very helpful, thank you so much! I had no idea of the database functionality within QGIS. This solved my problem! – Mathlete Jul 27 '15 at 23:13
  • @Mathlete could you please accept this answer if happy with it? :) It is the check mark next to the left top corner of the answer. – Miro Jul 28 '15 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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