Multiply count in select

Поиск
Список
Период
Сортировка
От M L
Тема Multiply count in select
Дата
Msg-id c11212cc0903251510p40b0b3e8vfcbf2e6468275a71@mail.gmail.com
обсуждение исходный текст
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Baron Schwartz
Дата:
Сообщение: Announcement - PostgreSQL Performance Conference
Следующее
От: Martin Gainty
Дата:
Сообщение: Re: Weird encoding behavior