?
Solved

Table of results SQL squery

Posted on 2003-03-16
5
Medium Priority
?
179 Views
Last Modified: 2013-12-24
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
Comment
Question by:CraigBFG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 8146764
Hi craig,

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

Thanks
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8146992
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
 
LVL 14

Accepted Solution

by:
hhammash earned 150 total points
ID: 8147004
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
 

Author Comment

by:CraigBFG
ID: 8147224
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
 
LVL 14

Expert Comment

by:hhammash
ID: 8147265
Thank you Craig,

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

hhammash
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question