List:General Discussion« Previous MessageNext Message »
From:Gary Broughton Date:January 23 2004 2:40pm
Subject:SELECT statement
View as plain text  
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

Order by problemSagar C Nannapaneni23 Jan
  • Re: Order by problemMartijn Tonies23 Jan
  • Re: Order by problemBenoit St-Jean23 Jan
  • Re: Order by problemFrederic Wenzel23 Jan
    • SELECT statementGary Broughton23 Jan
    • Re: Order by problemmos23 Jan
  • Re: Order by problemmos23 Jan