2

I have a data set that looks like this:

CREATE TABLE game
AS
  SELECT team_name, match_id, sections_won
  FROM ( VALUES
    ( 'Team A', 6  , 4 ),
    ( 'Team B', 6  , 1 ),
    ( 'Team A', 19 , 4 ),
    ( 'Team A', 8  , 4 ),
    ( 'Team B', 19 , 1 ),
    ( 'Team B', 8  , 1 ),
    ( 'Team A', 7  , 4 ),
    ( 'Team B', 7  , 1 )
  ) AS t(team_name, match_id, sections_won);

It's for a game, so teams compete in matches. The match has a possible 5 points and in these examples 1 team is winning 4 of the 5 sections

I'd like to pivot this into a scoreboard where the report looks like this:

Name    Match 6 Match 7 Match 8 Match 19
Team A  4       4       4       4
Team B  1       1       1       1

I've done this kind of thing before but I can't get this one to work for some reason.

The query looks a little like this. I tried to simplify for examples sake.

select *
from crosstab(
  $$
    select game.team_name::text, game.match_id, sum(game.rank)::int4
    from game
    where rank = 1 -- i only want the sections the team won, then sum above.
    group by 1,2
 $$
  ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

The resulting data set looks like this though, which I don't get:

Name    Match 6 Match 7 Match 8 Match 19
team a  4       null    null    null     
team b  1       null    null    null   
team a  4       4       null    null
team b  1       1       null    null
team a  4       null    null    null   
team b  1       null    null    null   

Any thoughts? I've spent several hours on this puppy so far. Driving me nuts! :)

  • 1
    are sections_won and rank the same? – Evan Carroll Mar 22 '17 at 15:27
  • 1
    Thanks for the edit Evan! I should have done that probably. – jacklin Mar 22 '17 at 15:33
  • oh wow! So adding the order by did work! @McNets I'm not sure I fully understand why though? – jacklin Mar 22 '17 at 15:34
  • 1
    @EvanCarroll answer is crystal clear. – McNets Mar 22 '17 at 15:35
2

You don't need the GROUP BY in a pivot... And, if you put that WHERE clause in there you're filtering out stuff you need.

SELECT *
FROM crosstab(
  $$
    SELECT game.team_name::text, game.match_id, game.sections_won
    FROM game
    ORDER by 1,2
 $$
  ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

  Team  | Match 6 | Match 7 | Match 8 | Match 19 
--------+---------+---------+---------+----------
 Team A |       4 |       4 |       4 |        4
 Team B |       1 |       1 |       1 |        1
(2 rows)

As to why you need the ORDER BY, from the docs

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.

  • This is great, thanks so much for your help @EvanCarroll, and thank you as well @McNets! – jacklin Mar 22 '17 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.