I'm attempting to collate a webpage showing results by various football teams in various cup competitions, and am trying to minimise the number of selects as best I can.
What I'm trying to get out in one statement is the number of home matches played by Burnley, how many they've won, drawn and lost, and the totals goals scored by them and against them. In an ideal world it would be something like the following, but I know this will not work: SELECT COUNT(*) AS matchesplayed, COUNT(result='H') AS homewins, COUNT(result='D') AS drawngames, COUNT(result='A') AS awaywins, SUM(homescore) AS homegoalsscored, SUM(awayscore) AS awaygoalsscored FROM matchstats WHERE (homeTeam = 'Burnley') AND (competition = 'F.A.Cup') Effectively it's like a grouping, but the goals columns are calculations of the entire number of matches. I can achieve it with two statements, but wondered if there was a way of combining the two? Many thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
