Databases
StackOverflow2013
Postsdbo
POSummarize totals given multiple conditions
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSummarize totals given multiple conditions
    text
    copied!<p>So I have two tables, players and matches with structures like so:</p> <pre><code> Table "public.players" Column | Type | Modifiers --------+---------+------------------------------------------------------ id | integer | not null default nextval('players_id_seq'::regclass) name | text | wins | integer | default 0 loses | integer | default 0 rating | integer | default 1500 </code></pre> <p>and</p> <pre><code> Table "public.matches" Column | Type | Modifiers ---------------+---------+------------------------------------------------------ id | integer | not null default nextval('matches_id_seq'::regclass) player1 | integer | player1rating | integer | player2 | integer | player2rating | integer | winner | boolean | </code></pre> <p>Where winner is true if player1 won that match or false if player2 won that match.</p> <p>Doing simple win/loss comparisons is easy using the players table, however I'm watching to do a comparison where two players have faced each other, what their records against each other are.</p> <p>So I'm confused on how I'd summarize where conditions given that a given player may be listed as either player1 or player2: P1 Wins: </p> <pre><code>(player1 = &lt;player1&gt; AND winner = true) OR (player2 = &lt;player1&gt; AND winner = false) </code></pre> <p>P2 Wins:</p> <pre><code>(player1 = &lt;player2&gt; AND winner = true) OR (player2 = &lt;player2&gt; AND winner = false) </code></pre> <p>Losses would just be the opposite of the other players wins.</p> <p>Something that returned information like:</p> <pre><code> id | name | wins | loses | rating | wins_v_opp | loses_v_opp ------+----------+------+-------+--------+------------+------------ 4200 | Sinku | 5 | 48 | 1191 | 1 | 4 4201 | Kenshiro | 33 | 29 | 1620 | 4 | 1 </code></pre> <p>What I came up with before update2 below:</p> <pre><code>CREATE FUNCTION matchup(text, text) AS $$ DECLARE player1_name ALIAS FOR $1; player2_name ALIAS FOR $2; BEGIN EXECUTE 'SELECT id FROM player WHERE name LIKE $1' INTO player1_id USING player1_name; IF NOT FOUND THEN RAISE EXCEPTION 'Player1 % not found', player1_name; END IF; EXECUTE 'SELECT id FROM player WHERE name LIKE $1' INTO player2_id USING player2_name; IF NOT FOUND THEN RAISE EXCEPTION 'Player2 % not found', player2_name; END IF; RETURN QUERY EXECUTE 'WITH cte_winners AS ( SELECT CASE WHEN winner THEN m.player1 ELSE m.player2 END AS player, COUNT(*) AS wins_v_opp, sum(count(*)) over() - COUNT(*) AS loses_v_opp FROM matches AS m WHERE player1 IN ($1,$2) AND player2 IN ($1,$2) GROUP BY player ) SELECT * FROM players AS p LEFT OUTER JOIN cte_winners AS cw ON cw.player = p.id WHERE p.id IN ($1,$2)' USING player1_id,player2_id; END; $$ LANGUAGE plpgsql; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload