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.
