How to get column ordered by average of difference of a field from some table in SQL

Go To StackoverFlow.com

1

I have a table like:

Table

I have to order the rows in function of the average of the difference of v field.

For example:

all f1:

v1= 1;
v2 = 10;
v3 = 451;
average(f1) = ((abs(1-10))+(abs(10-451)))/3;
average(f2) = ....

so I have to have the columns ordered by this average in descendent mode.

Is possible to do with one SQL query? Can someone help me?

2012-04-05 16:56
by JackTurky
Which sql variant are you using? MySQL? SQL Server 2008 - Ben English 2012-04-05 17:01
Is field v supposed to be in the final output - Matt Fenwick 2012-04-05 17:02
@MattFenwick no, it has no matte - JackTurky 2012-04-05 17:03
@ben i'm using sqlite over jdb - JackTurky 2012-04-05 17:03


5

You don't have to calculate the difference between all values, you only need to know the smallest, the largest, and the number of values.

If you look at these values:

( abs(1-10) + abs(10-451) ) / 3

If you just always subtract the smaller from the larger, you don't need the abs:

( (10-1) + (451-10) ) / 3

The inner parentheses aren't needed, so you get:

(10 - 1 + 451 - 10) / 3

Here you can elliminate the inner value 10, as you have +10 and -10. You end up with just the largest and the smallest values:

(451 - 1) / 3

It doesn't matter how many middle values you have, they will always be elliminated by themselves, e.g. (b-a)+(c-b)+(d-c)+(e-d)+(f-e) = (f-a).

So, the SQL for this would be:

select name, (max(v) - min(v)) / count(*) as averageDiff
from TheTable
group by name
order by averageDiff desc

Note: I'm not sure what this average of differences is supposed to mean, but you are dividing the average differences with the number of items, but you might want to divide it with the number of differences instead, i.e one less than the number of items; (count(*) - 1).

2012-04-05 17:09
by Guffa
good answer.. i will try.. cause after i need that difference because i have to show them on a char - JackTurky 2012-04-05 17:13
Ads