SELECT
case when (final_hand like 'high card%') then 'High card' ELSE
case when (final_hand like 'a pair%') then 'A pair' ELSE
CASE WHEN (final_hand like 'two pair%') THEN 'Two pair' ELSE
CASE WHEN (final_hand like 'three of a kind%') THEN 'Three of a kind' ELSE
CASE WHEN (final_hand like 'a straight%') then 'a straight' else
case when (final_hand like 'a flush%') then 'a flush' else
case when (final_hand like 'a full house%') then 'a full house' else
case when (final_hand like 'four of a kind%') then 'four of a kind' else
case when (final_hand like 'a straight flush%') then 'a straight flush' end end end end end end end end end as final_hand_ab,
/*not sure how to sum final_hands without using a redundant subquery so i was running the query once, 
totaling the raw count by hand, and then using that as TOTALNUMBER. ghetto, i know...*/
--round(count(final_hand)::numeric/TOTALNUMBER*100,2) AS final_hand_count
count(final_hand) AS final_hand_count
FROM
(SELECT
*,
substring(hh.hand_history, 'RIVER.*?bets \\$([\\d.]*).*?SUMM')::numeric as the_flop_bet,
substring(hh.hand_history, 'RIVER.*?raises to \\$([\\d.]*).*?SUMM')::numeric as the_flop_raise
FROM game g
JOIN hand_histories hh ON g.game_number = hh.game_number
JOIN game_players gp ON g.game_id = gp.game_id
JOIN players pl ON pl.player_id = gp.player_id
AND hh.hand_history ~ ('FLOP.*?' || pl.screen_name || '[\\w\\s\\(\\)]*? raises.*?TURN')
AND gp.went_to_showdown_n = 1)
AS flop_raises
WHERE the_flop_raise < the_flop_bet * 2.1
GROUP BY final_hand_ab ORDER BY final_hand_count DESC;