5

I have a table with 2 fields, field1 contains site specific data, field2 contains a number which groups the field1:

Field1, Field2
180, 1 
203, 1
220, 1
205, 1 
160, 2 
140, 2 
135, 2

I want to sort low to high of field1 grouped by field2.

I then want to take the "rank# of field1 grouped by field 2" SUBTRACT the "median of field1 grouped by field2" and divide the answer by "the total # of points of the field2 group"

I'm looking for some direction, a few ideas or a formula I can use in QGIS field calculator to help solve this problem. Or some idea's on a python script for the advanced python calculator.

Improve this question
2
  • 1
    Add fields Rank, F1_Med and F2_Cnt and calculate those first, if you can't calculate using QGIS use Excel if you have to but don't save the dbf, rather as CSV and join the table... this would now make your sorting much easier.
    – Michael Stimson
    Feb 28, 2017 at 21:19
  • Ok thanks I feared I may have to stick to excel for those calculations and it looks like I may have to. Thank you Michael
    – Dan B
    Feb 28, 2017 at 21:54

1 Answer 1

Reset to default
4

I think you can solve this calculation easily in QGIS by using "Virtual Layer" with SQL. Virtual layer

Do the calculation by using SQL:

SELECT field2, (max(field1) - AVG(field1))/count(field1)
FROM layer
GROUP BY field2

SQl:calculation

Improve this answer
2
  • Thanks Cy T, I get the following error: Query preparation error on SELECT (max(field1) - AVG(field1))/count(field1) FROM_tview LIMIT 1 Any ideas what causes this error? I'm running QGIS 2.14.11-Essen LTR.
    – Dan B
    Mar 1, 2017 at 14:52
  • I think it because of your SQL query. The functions of max(), avg() and count() can't work without "group by". Can you try the sql query again? "SELECT field2, (max(field1) - AVG(field1))/count(field1) FROM tablename GROUP BY field2"
    – Cy T
    Mar 2, 2017 at 0:50

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.