Antonio Conte Posted November 11, 2011 Share Posted November 11, 2011 Hey Note: This query is actually returning the right/correct data. You may skip the initial explanation... I'm currently working on a large query with a lot of subqueries. What I need to do, is to count the total number of games won, defeated and undecided. (by example, a 1-1 score). The count should be based on a specific team and also differ between home and away matches. This is the structure of the result from the query: Team / home_wins / home_defeats / home_undecided / away_wins / away_defeats / away_undecided What I'm wondering: The problem is that subqueries cannot return more than ONE result. Because of this, the calculations needs to be performed on a per-league-bases. Because I'll still need similar functionality next season, I'm wondering if I could make a function/use a changable variable here. It should be possible to change the league_id for this calculation every year, or even better, find a way to calculate each league of it's own. I just don't think that's possible. Here's the query: SET @league_id = 11; SET @club_id = 11; SELECT games.league_id AS league_id, league.name AS league, ( SELECT SUM(goals_club1 > goals_club2) FROM cnk_soccer_games WHERE club1_id = @club_id AND league_id = @league_id ) AS home_wins, ( SELECT SUM(goals_club1 = goals_club2) FROM cnk_soccer_games WHERE club1_id = @club_id AND league_id = @league_id ) AS home_undecided, ( SELECT SUM(goals_club1 < goals_club2) FROM cnk_soccer_games WHERE club1_id = @club_id AND league_id = @league_id ) AS home_lost, ( SELECT SUM(goals_club1 < goals_club2) FROM cnk_soccer_games WHERE club2_id = @club_id AND league_id = @league_id ) AS away_wins, ( SELECT SUM(goals_club1 = goals_club2) FROM cnk_soccer_games WHERE club2_id = @club_id AND league_id = @league_id ) AS away_undecided, ( SELECT SUM(goals_club1 > goals_club2) FROM cnk_soccer_games WHERE club2_id = @club_id AND league_id = @league_id ) AS away_lost FROM cnk_soccer_games AS games INNER JOIN cnk_soccer_league AS league ON ( games.league_id = league.id ) WHERE ( games.goals_club1 IS NOT NULL OR games.goals_club2 IS NOT NULL ) AND league_id = @league_id GROUP BY games.league_id An explanation of the query: - The query is only counting games from league_id 11 - Only counting games where the score is not NULL (the games are already played) - The results are grouped on a per-league-basis - Each subquery checks the score of club1 to that of club2 (home and away team) - The result of the sub-queries depends on the comparisons (should the home/away team win?).... And to wheater OUR team is the home or away team Hope someone can help me with this. Link to comment Share on other sites More sharing options...
Larry Posted November 11, 2011 Share Posted November 11, 2011 I meant to get to this today but ran out of time. I'll come back to it on Monday. Link to comment Share on other sites More sharing options...
Antonio Conte Posted November 17, 2011 Author Share Posted November 17, 2011 Just bumping this in case you forgot about it. Link to comment Share on other sites More sharing options...
Larry Posted November 19, 2011 Share Posted November 19, 2011 Yeah, yeah. Thanks. Monday disappeared, along with Wednesday and most of Friday. Okay, sorry about the delay. So, not a great answer yet, but it seems to me that this is a good occasion to use a VIEW. Anytime I have a complicated query that I'd like to turn into a simple query, I think of a VIEW. Also, you don't explain what your schema is, but it's possible that a change there would make this easier. As for changing this from year to year, that should be quite easy using a variable. Link to comment Share on other sites More sharing options...
Recommended Posts