From: | Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> |
---|---|
To: | Janning Vygen <vygen(at)gmx(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Joining three data sources. |
Date: | 2002-06-19 14:09:02 |
Message-ID: | 20020619230842.91E2.RK73@sea.plala.or.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
> How can i select all games with their results in a tabel like this:
> (i dont care about the team names. this is easy to achieve of course,
> my question is just about the goals)
>
> team1_id|team2_id|goals1|goals2
> 1 2 2 1
How about something like this:
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;
P.S.
As for Goal table, if it has a large number of the rows, you maybe
need to create a unique index on it.
CREATE UNIQUE INDEX idx_goal ON goal(game_id, team_id, minute);
Regards,
Masaru Sugawara
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-06-19 14:18:51 | Re: SQL performance issue with PostgreSQL compared to MySQL |
Previous Message | Jeff Self | 2002-06-19 13:26:52 | SQL performance issue with PostgreSQL compared to MySQL |