[SOLVED] DESC help

[mak_gillett]

I have a program which enters individual football game stats into a mysql database then when I recall I want to have the stats averaged and sorted decsending.

 

So each row in the database has the following data:

 

Id, Tag, PassingStats

 

When I get the data I use: Select tag, AVG(PassingStats) FROM mystats GROUP BY tag ORDER BY passingstats DESC;

 

What is happening however is if one Tag has more entries than another it will not be ordered properly.

 

So Basically the database looks like:

 

--------------------------

| ID | Tag | PassingStats |

--------------------------

| 1 | Mike | 100              |

| 2 | Mike | 150              |

| 3 | John | 200              |

--------------------------

 

When it is displayed after averaging it will be:

 

----------------------

| Tag | PassingStats |

----------------------

| Mike | 125            |

| John | 200            |

----------------------

 

Even though it is supposed to be in decsending order.

 

Now then, if there are the same amount of records for each Tag it will display correctly:

 

----------------------

| Tag | PassingStats |

----------------------

| Mike | 100            |

| Mike | 150            |

| John | 200          |

| John | 250          |

---------------------

 

Will display as:

 

---------------------

| Tag | PassingStats|

---------------------

| John | 225          |

| Mike | 125          |

---------------------

 

Any help would greatly appreciated.

 

 

[Illusion]

try this

Select tag, AVG(PassingStats) FROM mystats GROUP BY tag ORDER BY NULL,passingstats DESC;

[mak_gillett]

try this

Select tag, AVG(PassingStats) FROM mystats GROUP BY tag ORDER BY NULL,passingstats DESC;

 

I will give it a shot during my lunch break and get back to you.  Thanks for the quick response.

[mak_gillett]

try this

Select tag, AVG(PassingStats) FROM mystats GROUP BY tag ORDER BY NULL,passingstats DESC;

 

That didn't seem to work.  Here is the actual code:

 

<?php

$color1 = "#FFFFFF";

$color2 = "#999999";

$row_count = "0";

$query = "SELECT EATag, AVG(PassFor) FROM mystats GROUP BY EATag ORDER BY NULL,PassFor DESC LIMIT 0, 5"; $result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))

{

$row_color = ($row_count % 2) ? $color1 : $color2;

?>

<tr>

<td width="280" bgcolor="<?php echo $row_color; ?>"><?php  echo $row['EATag']; ?></td>

<td width="110" bgcolor="<?php echo $row_color; ?>"><?php  echo number_format($row['AVG(YardsFor)'], 1); ?></td>

<?php

$row_count++; }

?>

 

 

 

[Illusion]

sorry yaar,

 

first try this

 

Select tag, AVG(PassingStats) as average FROM mystats GROUP BY tag ORDER BY average DESC

 

if it doesn't work , try this

 

Select tag, AVG(PassingStats) as average FROM mystats GROUP BY tag ORDER BY NULL,average DESC

[mak_gillett]

sorry yaar,

 

first try this

 

Select tag, AVG(PassingStats) as average FROM mystats GROUP BY tag ORDER BY average DESC

 

if it doesn't work , try this

 

Select tag, AVG(PassingStats) as average FROM mystats GROUP BY tag ORDER BY NULL,average DESC

 

Didn't think about that, I'll try when I get home.

[mak_gillett]

sorry yaar,

 

first try this

 

Select tag, AVG(PassingStats) as average FROM mystats GROUP BY tag ORDER BY average DESC

 

if it doesn't work , try this

 

Select tag, AVG(PassingStats) as average FROM mystats GROUP BY tag ORDER BY NULL,average DESC

 

Well that didn't fix it either.  Instead of averageing the numbers it is displaying everything as 0.0

 

 

[mak_gillett]

Nevermind I figured it out was I wasn't doing after changing it to your code.

 

I thank you for the help and it is working.  For further use here is what I changed it to:

 

<?php

$color1 = "#999999";

$color2 = "#333333";

$row_count = "0";

 

$query = "SELECT EATag, AVG(RushFor) as average FROM mystats GROUP BY EATag ORDER BY average DESC LIMIT 0, 5";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))

{

$row_color = ($row_count % 2) ? $color1 : $color2;

?>

<tr>

<td width="280" bgcolor="<?php echo $row_color; ?>"><font size="2" color="#FFFFFF" face="Courier New, Courier, monospace"><?php  echo $row['EATag']; ?></font></td>

<td width="110" bgcolor="<?php echo $row_color; ?>"><font size="2" color="#FFFFFF" face="Courier New, Courier, monospace"><?php  echo number_format($row['average'], 1); ?></font></td>

<?php

$row_count++;

}

?>