rollup using subquery select p.id, points, matches_won from --sum the subquery first before joining (select id, sum(matches_won)as matches_won from matches group by id) m on p.id = m.id add a file to the join statement from points_table p join matches_group m on p.id = m.id and p.year = m.year -- Update the subquery to join on a second field ON c.country_id = w.country_id AND w.year = CAST(c.year AS date) GROUP BY w.country_id) AS subquery; -- Add the three medal fields using one sum function SUM(COALESCE(bronze,0) + COALESCE(silver,0) + COALESCE(gold,0)) AS medals, SUM(COALESCE(bronze,0) + COALESCE(silver,0) + COALESCE(gold,0)) / CAST(cs.pop_in_millions AS float) AS medals_per_million
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter