MYSQL Problems with "order by"

[csmith3pmg]

I have a table made up of Year, Week, Name, ID, and then several numeric fields defined as either smallint(3) or smallint(4). I am trying to create two result sets from very similar queries the first is working and the second is not. The first is:

select Name, ID, Sum(1stNumField) as Num1, Sum(2ndNumField) as Num2, etc
from Table
group by Name, ID
order by Num1 Desc

The second is:

select Name, ID, Format(Avg(1stNumField), 2) as Num1, Format(Avg(2ndNumField), 2) as Num2, etc
from Table
group by Name, ID
order by Num1 Desc

For some reason the second will return the results but not ordered the way that I am expecting. I believe that it is ordering by the 1stNumField instead of the average of that field.

Can anyone explain why or help me get the ordering based on the average?

Thanks in advance,
Chris

 

[GiffordS]

Have you tried breaking the format up?

select Name, ID, sum(1stNumField) as Num1, sum)2ndNumField) as Num2,
avg(Num1) as "anum1",
avg(Num2) as "anum2",
from Table
group by Name, ID
order by anum1 Desc;

Not sure, but maybe.