[Resolved] query help - mathematical expressions as fields possible? and more

[akillez]

I've been hammering on this for a few day but can't get out of it, and am about to call it quits and just create a new table wich holds the results needed...

Maybe one of you smart and more experienced people can find a solution?

I have one table with game results (1bb), and one table containing teamnames (teams).

The first table has the fields Home, Away, HScore (HomeScore) and AScore (AwayScore).
The second table has the fields id and name.

What i want to do is create a ranking based on games played, amount won, draw and lost and order that by a win average.

the query should return the following for each teamid and order by average,games_played:
- name:
name of the team as found in table teams
- games_played:
count all rows where 1bb.Home or 1bb.Away = teamid and HScore != null
- amount_won:
if Home = teamid and HScore > Ascore then amount_won + 1
if Away = teamid and HScore < Ascore then amount_won + 1
- amount_draw:
if Home or Away = teamid and HScore=Ascore and HScore != null then amount_draw +1
- amount_lost:
games_played - (amount_won + amount_draw) = amount_lost
- average:
(amount_won+(amount_draw/2))/amount_played

[Roger_Ramjet]

Yes! Is the short answer. You can use expressions as fields, and you use AS to name them.

What you need for this problem is GROUP BY and the aggregate functions: http://dev.mysql.com/doc/mysql/en/GROUP-BY-Modifiers.html

I do think you need to modify the table designs as well.

[akillez]

thanx, i'll look into it...

[FMB]

best thing to do is create a loop for the table teams, for each team get the info from the 1bb table and store it in an array.
Make sure that the avg column is the first column so you can sort the array and then display all results in a table.

[akillez]

IT WORKS! 😃 😃 😃

$page = "1bb";

$name_array = array();
$ranking    = array();

$q_names = "SELECT * FROM teams WHERE id LIKE '".$page."%'";
$r_names = mysql_query($q_names);

while($row = mysql_fetch_row($r_names)) {
  $team_array   = array($row[0],$row[1]);
  $name_array[] = $team_array;
}

for($i=0;$i<count($name_array);$i++) {
  $q_won_h  = "SELECT HScore FROM ".$page." WHERE Home='".$name_array[$i][0]."' AND HScore > AScore";
  $q_won_a  = "SELECT HScore FROM ".$page." WHERE Away='".$name_array[$i][0]."' AND HScore < AScore";
  $q_lost_h = "SELECT HScore FROM ".$page." WHERE Home='".$name_array[$i][0]."' AND HScore < AScore";
  $q_lost_a = "SELECT HScore FROM ".$page." WHERE Away='".$name_array[$i][0]."' AND HScore > AScore";
  $q_draw   = "SELECT Hscore FROM ".$page." WHERE (Home='".$name_array[$i][0]."' OR Away='".$name_array[$i][0]."') AND HScore = AScore";

  $r_won_h  = mysql_query($q_won_h);
  $r_won_a  = mysql_query($q_won_a);
  $r_lost_h = mysql_query($q_lost_h);
  $r_lost_a = mysql_query($q_lost_a);
  $r_draw   = mysql_query($q_draw);

  $amount_won    = mysql_num_rows($r_won_h) + mysql_num_rows($r_won_a);
  $amount_lost   = mysql_num_rows($r_lost_h) + mysql_num_rows($r_lost_a);
  $amount_draw   = mysql_num_rows($r_draw);
  $amount_played = $amount_won + $amount_lost + $amount_draw;
  $avg           = ($amount_won + ($amount_draw / 2)) / $amount_played;

  $teamstats = array("A" => number_format($avg,3),
                     "N" => $name_array[$i][1],
                     "P" => $amount_played,
                     "W" => $amount_won,
                     "L" => $amount_lost,
                     "D" => $amount_draw);
  $ranking[$i] = $teamstats;
}

foreach($ranking as $val) {
  $sortarray[] = $val["A"];
}
array_multisort($sortarray,SORT_DESC,$ranking);

for($j=0;$j<count($ranking);$j++) {
  $array = $ranking[$j]; 
  print($array['N']." - ".$array['P']." - ".$array['W']." - ".$array['L']." - ".$array['D']." - ".$array['A']."<BR>\n");
}