rawilkins
asked on
query problem
In This query
SELECT `players`.`Home Team`, `players`.`Visiting Team`, IF(`Score1` > `Score2`, 1, 0) AS `Win`, IF(`Score2` > `Score1`, 1, 0) AS `Loss`
FROM `players`
WHERE (`players`.`Date` > '2005-08-10')
I need to know which syntax will work best for me, group,sort, order by or a combonitation of some to get this result.
I want to have the Team whether Home or Visting to be listed in order of most wins, a standing based on the win loss record.
SELECT `players`.`Home Team`, `players`.`Visiting Team`, IF(`Score1` > `Score2`, 1, 0) AS `Win`, IF(`Score2` > `Score1`, 1, 0) AS `Loss`
FROM `players`
WHERE (`players`.`Date` > '2005-08-10')
I need to know which syntax will work best for me, group,sort, order by or a combonitation of some to get this result.
I want to have the Team whether Home or Visting to be listed in order of most wins, a standing based on the win loss record.
ASKER
Can you explain this part 'SELECT team,wins FROM' I do not have a field called team and wonder what I am missing. Will it create a team name based on 'Home Team' entries?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in general, you will eventually want to use SUM to determine which team has the most wins (sum the wins up), then order by (wins column) desc (so most wins are on top). just remember that when using sum, you will need to group by the other columns.
i believe the query could go something like:
SELECT team,wins FROM (SELECT `players`.`Home Team` AS team, IF(`Score1` > `Score2`, 1, 0) AS `wins` FROM players GROUP BY `players`.`Home Team` WHERE (`players`.`Date` > '2005-08-10') UNION SELECT `players`.`Visiting Team` AS team, IF(`Score1` > `Score2`, 1, 0) AS `wins` FROM players GROUP BY `players`.`Visiting Team` WHERE (`players`.`Date` > '2005-08-10')) WHERE 1 ORDER BY `wins` DESC