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

I have a table like:

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?

Answers


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


Need Your Help

Why is Visual Studio not able to open .csproj files?

.net visual-studio-2008 xna xna-3.0

I have the odd problem that I am not able to open the properties of my .NET projects in Visual Studio. If I try to open it by clicking on the Properties tree node in the Solution Explorer I get the