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
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?