PostgreSQL group by and count on specific condition-CodePudding

I have a following tables (example)

Analyze_Line

id	game_id	bet_result
1	1	WIN
2	2	LOSE
3	3	WIN
4	4	LOSE
5	5	LOSE
6	6	WIN

Game

id	league_id	home_team_id	away_team_id
1	1	1	2
2	2	2	3
3	3	3	4
4	1	1	2
5	2	2	3
6	3	3	4

Required Data:

league_id	WIN	LOSE	GameCnt
1	1	1	2
2	0	2	2
3	2	0	2

Analyze_Line table is joined with Gametable and simple can get GameCnt grouping by league_id, but I am not sure how to calculate WIN count and LOSE count in bet_result

CodePudding user response：

You can use conditionals in aggregate function to divide win and lose bet results per league.

select
  g.league_id,
  sum(case when a.bet_result = 'WIN' then 1 end) as win,
  sum(case when a.bet_result = 'LOSE' then 1 end) as lose,
  count(*) as gamecnt
from 
  game g
  inner join analyze_line a on
    g.id = a.game_id
group by 
  g.league_id

Since there is no mention of postgresql version, I can't recommend using FILTER clause (postgres specific), since it might not work for you.

CodePudding user response：

Adding to Kamil's answer - PostgreSQL introduced the filter clause in PostgreSQL 9.4, released about eight years ago (December 2014). At this point, I think it's safe enough to use in answers. IMHO, it's a tad more elegant than summing over a case expression, but it does have the drawback of being PostgreSQL specific syntax, and thus not portable:

SELECT   g.league_id,
         COUNT(*) FILTER (WHERE a.bet_result = 'WIN') AS win,
         COUNT(*) FILTER (WHERE a.bet_result = 'LOSE') AS lose,
         COUNT(*) AS gamecnt
FROM     game g
JOIN     analyze_line a ON g.id = a.game_id
GROUP BY g.league_id