You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
26 lines
827 B
26 lines
827 B
-- Add up migration script here
|
|
CREATE OR REPLACE VIEW player_points_view
|
|
AS SELECT
|
|
COUNT(shots.id) AS points,
|
|
COUNT(CASE WHEN shots.shooter = game_players.id THEN shots.id END) AS goals,
|
|
COUNT(CASE WHEN shots.assistant = game_players.id OR shots.assistant_second = game_players.id THEN shots.id END) AS assists,
|
|
periods.id AS period_id,
|
|
games.id AS game_id,
|
|
games.division AS division_id,
|
|
players.first_names,
|
|
players.last_name,
|
|
players.id
|
|
FROM players JOIN game_players
|
|
ON game_players.player = players.id
|
|
LEFT JOIN shots
|
|
ON shots.goal=true
|
|
AND (shots.shooter=game_players.id
|
|
OR shots.assistant=game_players.id
|
|
OR shots.assistant_second=game_players.id)
|
|
LEFT JOIN periods
|
|
ON periods.id=shots.period
|
|
LEFT JOIN games
|
|
ON games.id=periods.game
|
|
GROUP BY
|
|
players.id,division_id,game_id,period_id;
|