• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Table of results SQL squery

I have a table of results for a sports club with 3 teams. The table headings are :-

TeamId, Point_For, Point_Against, Played, Outcome. Where outcome can be either Won/Lost/Draw or cancelled.

The table should look like :-

T     PF    PA    Pl   W    L   D
1st - 500 - 100 - 25 - 20 - 3 - 2
2nd - 300 - 200 - 25 - 15 - 10 - 0
3rd - 420 - 200 - 25 - 18 - 5 - 2


The query I have at the moment sort the first 4 columns, but I can't get it to count the outcomes and seperate them into the respective colums W/L/D.

SELECT TeamID, count(Result_For), sum(result_for), sum(Result_against)
FROM tblResult
WHERE season='2002-2003'  
GROUP BY TeamID;

Hope that makes sense, can anyone help?
Craig
0
CraigBFG
Asked:
CraigBFG
  • 4
1 Solution
 
hhammashCommented:
Hi craig,

Can I see the table structure (table fields), I did not understand the points under W, L or D.

Thanks
hhammash
0
 
hhammashCommented:
Hi Craig,

SOLVED

SELECT Teams.TeamID, Sum(Teams.Point_for) AS PF, Sum(Teams.Point_against) AS PA, Sum(Teams.Played) AS PL, Sum(Right([Outcome]="won",1)) AS W, Sum(Right([Outcome]="lost",1)) AS L, Sum(Right([Outcome]="cancelled",1)) AS C, Sum(Right([Outcome]="draw",1)) AS D, Teams.Season
FROM Teams
GROUP BY Teams.TeamID, Teams.Season
HAVING (((Teams.Season)="2002-2003"));

Regards
hhammash
0
 
hhammashCommented:
Hi Carig,

You can either make this query in Access and make it your record source in the FrontPage DRW.  If you have a search form in the DRW to put the season in it.  You can make the SQL at the end like

SELECT Teams.TeamID AS T, Sum(Teams.Point_for) AS PF, Sum(Teams.Point_against) AS PA, Sum(Teams.Played) AS PL, Sum(Right([Outcome]="won",1)) AS W, Sum(Right([Outcome]="lost",1)) AS L, Sum(Right([Outcome]="cancelled",1)) AS C, Sum(Right([Outcome]="draw",1)) AS D, Teams.Season
FROM Teams
GROUP BY Teams.TeamID, Teams.Season
HAVING (((Teams.Season)='::SeasonTextSearch::'));

Regards
hhammash
0
 
CraigBFGAuthor Commented:
Thanks very much, your answer was close and with a little tweeking got it to work. Where you have double quotes, I had to replace these with single quotes, my final sql query looks like below. See www.ashbyrfc.co.uk for the working version

SELECT TeamID,
count(Result_For) as P,
Sum(Right([Result_Outcome]='won',1)) as W,
Sum(Right([Result_Outcome]='lost',1)) as L,
Sum(Right([Result_Outcome]='draw',1)) as D,
sum(result_for) as F,
sum(Result_against) as A
FROM tblResult
WHERE season='2002-2003'  
GROUP BY TeamID;
0
 
hhammashCommented:
Thank you Craig,

It was very nice of you to send the link.  It looks very good.

hhammash
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now