Am Mittwoch, 19. Juni 2002 16:09 schrieb Masaru Sugawara: > On Wed, 19 Jun 2002 12:33:47 +0200 > > Janning Vygen <[EMAIL PROTECTED]> wrote: > > --------------------- > > Result Inter Mailand vs. AC ROM 2:1 > > team1_id|team2_id|goals1|goals2 > > 1 2 2 1 > > SELECT go1.game_id, go1.team1_id, go1.team2_id, > SUM(CASE WHEN go2.team_id = go1.team1_id > THEN go2.n ELSE 0 END) AS goals1, > SUM(CASE WHEN go2.team_id = go1.team2_id > THEN go2.n ELSE 0 END) AS goals2 > FROM (SELECT game_id, > min(team_id) AS team1_id, > max(team_id) AS team2_id > FROM goal > GROUP BY 1) AS go1, > (SELECT game_id, team_id, count(*) AS n > FROM goal > GROUP BY 1, 2) AS go2 > WHERE go1.game_id = go2.game_id > GROUP BY 1, 2, 3;
Oh thanks a lot. You pushed me in the right direction. i still get headache when trying to write complicated selects. there was something wrong in your statement but i was able to correct it by myself. Thanks for your help!! Are you able to type those queries in minutes?? It seems so ... amazing! > As for Goal table, if it has a large number of the rows, you maybe > need to create a unique index on it. of course. it was just an example... kind regards janning ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
