SQL: How to get AVG (MIN (number))?

Question

I am looking for the MEDIUM (common) number MINIMUM (grouped by person).

My table looks like this:

Rank Name 1 Amy 2 Amy 3 Amy 2 Bart 1 Charlie 2 David 5 David 1 Ed 2 Frank 4 Frank 5 Frank 

I want to know AMONG the lowest ratings. For these people, the lowest ratings are:

 Rank Name 1 Amy 2 Bart 1 Charlie 2 David 1 Ed 2 Frank 

Giving me a final answer of 1.5 - because three people have MIN (Rank) 1 , and the other three have MIN (Rank) 2 . This is what I am looking for - one number.

My real data has a couple of hundred rows, so this is not very big. But I can’t figure out how to do this in one simple statement. Thanks for any help.

Answer 1

Choosing the minimum values ​​is simple. For cast (), integer division must be avoided . You can also avoid integer division by casting for float instead of decimal. (But you should be aware that floats are “useful approximations.”)

 select name, cast(min(rank) as decimal) as min_rank from Table1 group by name 

Now you can use the minimum values ​​as a common table expression and select from it.

 with minimums as ( select name, cast(min(rank) as decimal) as min_rank from Table1 group by name ) select avg(min_rank) avg_min_rank from minimums 

If you need to do the same on a platform that does not support common table expressions, you can: a) create a minimum view and select from this view, or b) use the minimum values ​​as derived tables.

Answer 2

Try the following:

 ;WITH MinScores AS ( SELECT "Rank", Name, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY "Rank") row_num FROM Table1 ) SELECT CAST(SUM("Rank") AS DECIMAL(10, 2)) / COUNT("Rank") FROM MinScores WHERE row_num = 1; 

SQL Fiddle Demo

Answer 3

You can try to use a view to get the lows, and then get the average minimum in an external query, as in:

 -- Get the avg min rank as a decimal select avg(MinRank * 1.0) as AvgRank from ( -- Get everyone min rank select min([Rank]) as MinRank from MyTable group by Name ) as a 

Answer 4

I think the easiest one will be

for max

 select name , max_rank = max(rank) from table group by name; 

for medium

 select name , avg_rank = avg(rank) from table cgroup by name;