Q:

Rails aggregate query counting rows that satisfy certain conditions

Let's say that have a table called bets.  I want to run an aggregate sql query that counts rows satisfying certain conditions.  For example, I want to return a count of all bets won, all bets lost, etc.  I also want these counts to be grouped by several different columns.  I tried a few different queries, but not getting the results I'd expect.  For example:
Bet.select("user_id, event_id, bet_line_id, pick, COUNT(state = 'won') AS bets_won,
COUNT(state = 'lost') AS bets_lost, COUNT(state = 'pushed') AS bets_pushed").
group('user_id, event_id, bet_line_id, pick')

Just gives me the result of "1" for bets_won or bets_lost or bets_pushed for any of the records returned.  Using Rails 3.2 + postgres.

A:

you have to pass case expression so it will return bigint value . 
Bet.select("user_id, event_id, bet_line_id, pick, 
  COUNT(CASE WHEN state = 'won' then 1 ELSE null END) AS bets_won,
  COUNT(CASE WHEN state = 'lost' then 1 ELSE null END) AS bets_lost, 
  COUNT(CASE WHEN state = 'pushed' then 1 ELSE null END) AS bets_pushed").
 group('user_id, event_id, bet_line_id, pick')

