From: | Janning Vygen <janning(at)vygen(dot)de> |
---|---|
To: | Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Joining three data sources. |
Date: | 2002-06-19 18:07:20 |
Message-ID: | 02061920072002.26278@konkordia |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Am Mittwoch, 19. Juni 2002 16:09 schrieb Masaru Sugawara:
> On Wed, 19 Jun 2002 12:33:47 +0200
>
> Janning Vygen <vygen(at)gmx(dot)de> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Wei Weng | 2002-06-19 19:27:01 | Which one is faster? |
Previous Message | Michael Agbaglo | 2002-06-19 18:03:28 | syntax for access an alias in the where clause ? |