So many days of Advent! So many chances to practice SQL!
Today, we’ll be working on the next in the series using the data and processes that we found yesterday in Day 2 Part 1 – found here.
Thankfully, we were smart when we began working through the data and we have the data for each of our dice in separate tables, so breaking the data apart has definitely paid off! Now we can do just a little bit of work with the data from yesterday and we’ll be ready to give the results!
First, we want to find the largest number of dice for each type. Since we’re using SQL, we can do this all in one fun query.
SELECT G.Game,
MAX(CONVERT(int, G.GreenRolls)) AS Green,
MAX(CONVERT(int, B.BlueRolls)) AS Blue,
MAX(CONVERT(int, R.RedRolls)) AS Red
INTO #TotalRollsDay2
FROM #GreenGame G
JOIN #BlueGame B ON B.Game = G.Game
JOIN #RedGame R ON R.Game = G.Game
GROUP BY G.Game
ORDER BY CONVERT(int, G.Game)Since all of these tables have all of the games showing in each of them, then we can do an INNER JOIN between all of our dice temp tables. If any of the games did not show up in one of the tables, we would have to do a LEFT JOIN so that all games would display. (Does anyone use a RIGHT JOIN on purpose? Maybe left-handed coders?)
Notice that we are using the CONVERT function here to make sure that the rolls are in integer format. Otherwise – at least on my version of the temp table – we have the fields showing as varchars. This, sadly, makes the data be ordered incorrectly – unless you believe that 10 is less than 2. Yes, varchars truly are ordered that way normally.
From here, we multiply each type of dice together (Green * Red * Blue) and add them up!
Nothing too bad today. The biggest thing to remember is to make sure that you’re using the correct data type. Beyond that, this section should be fairly easy.
With that, I hope everyone is enjoying the #AdventOfCode and that we’ll see you again tomorrow!