DEV Community

Discussion on: #SQL30 Day 4: Video Game Sales

View post
Collapse
 
smason profile image
Sam Mason •

I'm a strong believer in Hadley Wickham's "tidy data" and think this sort of transform shouldn't belong in the database! I presume you're going to use the tablefunc module, but then columns can appear and disappear and break other downstream code/queries. I'd therefore go with the longer and more explicit:

SELECT year,
  SUM(CASE genre WHEN 'Action'       THEN global_sales END) AS "Action",
  SUM(CASE genre WHEN 'Adventure'    THEN global_sales END) AS "Adventure",
  SUM(CASE genre WHEN 'Fighting'     THEN global_sales END) AS "Fighting",
  SUM(CASE genre WHEN 'Platform'     THEN global_sales END) AS "Platform",
  SUM(CASE genre WHEN 'Puzzle'       THEN global_sales END) AS "Puzzle",
  SUM(CASE genre WHEN 'Racing'       THEN global_sales END) AS "Racing",
  SUM(CASE genre WHEN 'Role-Playing' THEN global_sales END) AS "Role-Playing",
  SUM(CASE genre WHEN 'Shooter'      THEN global_sales END) AS "Shooter",
  SUM(CASE genre WHEN 'Simulation'   THEN global_sales END) AS "Simulation",
  SUM(CASE genre WHEN 'Sports'       THEN global_sales END) AS "Sports",
  SUM(CASE genre WHEN 'Strategy'     THEN global_sales END) AS "Strategy",
  SUM(CASE genre WHEN 'Misc'         THEN global_sales END) AS "Misc"
FROM videogame
GROUP BY 1
ORDER BY 1

which also lets me place the Misc genre at the end rather than awkwardly in the middle!

I found it useful to run this:

SELECT genre, SUM(global_sales), COUNT(*),
  SUM(na_sales + eu_sales + jp_sales + other_sales)
FROM videogame
GROUP BY 1 ORDER BY 1 LIMIT 50;

first to find out the possible values, with the LIMIT on in case of crazy data or schema misunderstandings. It also lets me do a quick check of the various _sales columns adding up to the global_sales column, again to make sure I understand the question and data.