SQL Problem, League Table Presentation of information
Hey guys, the following link is the design of my database
http://www.upload-picture.com/images/C0p45676.jpg
I'm trying to get SQL code, to make a league table, but i'm having trouble counting up the necessary game points. All the points are stored within the Tbl_Fixtures. I'm trying to get a list of Teams, given within the Table Tbl_Teams (Team_Name), with the respective amount of total points won.
In Tbl_Fixtures, the selected team can be either Home_Team or Away_Team, and points for these are Team1_Score (for home_team) and Team2_Score (for away_team).
I've tryed using SUM but i get a error message about values not specified for agregate functions, and 'this only gives back one vale' or something like those.
Can anyone put me in the right direction, i've tryed alot of different SQL code but i keep getting errors :(
Thanks for you help.
# 1
> ... "or something like those" ...
> ... "but i keep getting errors" ...
If you want us to help you, write down the actual errors in exact phrase. Those aren't intented for phun and are very helpful in troubleshooting.
Or just do it yourself, it's easy: do a Google search on the errors in the exact phrase.
Anyway, it just looks like that you haven't aggregated (grouped) the desired values.
# 2
Alrighty here's the code i've just tried just to display the Team Names, Total Points for each team, from home games(team1_Score), and away games(team1_Score) where League_ID = 1
SELECT t.Team_Name AS Team,
SUM(CASE WHEN (f.Home_Team = t.Team_Name) THEN team1_Score ELSE 0
WHEN( (f.Away_Team = t.Team_Name) THEN team2_Score ELSE 0 END) as TotalPoints
FROM Tbl_Fixtures f, Tbl_Teams t
WHERE t.League_ID = 1
GROUP BY t.Team_Name
And i get the error:
"IErrorInfo.GetDescription failed with E_FAIL (0x80004005)."
Maybe this isn't the best way to work out the total points but i can't figure out a better way :( and i've tried quite a few!
# 3
in the code ignore the ( after the second WHEN, that was a typo, but the error stays the same!
# 4
> Alrighty here's the code i've just tried just to
> display the Team Names, Total Points for each team,
> from home games(team1_Score), and away
> games(team1_Score) where League_ID = 1
>
> SELECT t.Team_Name AS Team,
> SUM(CASE WHEN (f.Home_Team = t.Team_Name) THEN
> team1_Score ELSE 0
> WHEN( (f.Away_Team = t.Team_Name) THEN team2_Score
> ELSE 0 END) as TotalPoints
> FROM Tbl_Fixtures f, Tbl_Teams t
> WHERE t.League_ID = 1
> GROUP BY t.Team_Name
>
> And i get the error:
>
> "IErrorInfo.GetDescription failed with E_FAIL
> (0x80004005)."
>
> Maybe this isn't the best way to work out the total
> points but i can't figure out a better way :( and
> i've tried quite a few!
Timmy, what DB is this ... ?
# 5
> Timmy, what DB is this ... ?It's not a database. It's Access (shudder). See the screenshot.D.
# 6
yeah it is access, and its making me angry! but as i'm trying to create and easy to use system, i thought alot more people can edit info using access, as they are familiar with it. But now i'm getting porblems :(nightmare!
# 7
Hey guys, i'm wondering if the problem can't be solved with the applications i'm using, cos no one seems to be able to suggest anything? Should i be using a different language to get the wanted effect described in the problem? or is it a lost cause?
# 8
I can't speak for anyone else, but from my pov it's basically a SQL problem, not a Java issue, and so I'm not that interested.
# 9
I think it's an SQL problem too. I'm surprised nobody has suggested that having... ELSE 0 WHEN ...in that CASE clause isn't right.
