Multiply count in select

From: M L <novemberox(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Multiply count in select
Date: 2009-03-25 22:10:03
Message-ID: c11212cc0903251510p40b0b3e8vfcbf2e6468275a71@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi there, I was trying this query:
SELECT player.name, pos.position, count(event.event_id) AS APP,
count(goal.event_id) AS GOAL
FROM t_events event, t_events goal, t_players player, t_positions pos
WHERE player.position_id=pos.id
AND player.team_id=2
AND event.player_id=player.id
AND goal.player_id=player.id
AND goal.event_id=1
AND event.event_id=4
GROUP BY player.name, pos.position;

but it gave me result
name | position | app | goal
-----------+------------+-----+------
AdeJaWoR. | forward | 3 | 3
Ronnie | defender | 18 | 18
Parciez | midfielder | 54 | 54
Trzmielu | defender | 18 | 18
_Domin_ | forward | 64 | 64
Muffin | midfielder | 30 | 30

And that wasn't my intention. I have table:
Table "public.t_events"
Column | Type |
Modifiers
-----------+----------+-------------------------------------------------------
id | integer | not null default
nextval('t_events_id_seq'::regclass)
player_id | integer | not null
match_id | integer | not null
event_id | integer | not null
time | smallint |
team_id | integer | not null

Others are just stuff to connect ids with real names, positions. How can I
get list of players with positions and count of goals and appearances? In
table t_events is field event_id where 1 means goal, 2 assist, 3 own goal
etc.

Browse pgsql-general by date

  From Date Subject
Next Message Baron Schwartz 2009-03-25 22:53:11 Announcement - PostgreSQL Performance Conference
Previous Message Scott Marlowe 2009-03-25 21:52:43 Re: Parallel Query Processing