Let’s start with the database of runners that we’ve built in our last lesson.

We have data on the races that runners ran and their race results. A helpful statistic that we could provide to the users of the database could be the number of races that each runner has ran. Please try the code below.

When you run the MySQL code above, you could see that is displays a table that lists the runner IDs in order and next to it, shows the number of records within the race table it had for that specific runner ID. In the code, you could see that we’ve used the COUNT aggregate function, and simply counted the number of occurrences for that ID. An important point here is GROUP BY. You could see that it wash grouped by runner_id, meaning that the records for each runner_id was considered as a “group” and the aggregate function COUNT was executed within that group. So overall, we were able to achieve the intended output.

Are you were looking at the output and was thinking that it would be better if the names of the runners were displayed instead of just the IDs, we like where you’re headed! We could quickly utilize joins that we learned in our previous lesson to achieve this. Please try it out yourself, and then check the code below.

Now let’s think of a different scenario. In running, the longest you’ve ran is meaningful. How would you create a table that lists the longest time period that a runner has ran in a race? Try making any changes to your code to achieve this and then check the example below.

This time, let’s look at some code first and thinking through what it’s intending to do. Check out the MySQL code below.

Were you able to read through the code and understand the intention? This will display a table that lists all the individual race results accompanied by the distance of the course and the individual race result. Now try reading through a variation of this code below.

With the dataset that was created in the previous code, you could now see that we are grouping it by course and using the aggregate functions AVG and MIN. Go ahead and try out this example and you might see an output like this:

Now we’ve created a nice concise table of each course, the averaging timing it took for the runners in the club to complete the course accompanied by the best race results any runner has had in that course. You could quickly notice that we could create various analyses on our data to provide helpful insights combining aggregate functions with all the other MySQL techniques we’ve learned so far! If you are feeling empowered to create more helpful analyses, that’s awesome!

Let’s now think of creating some stats for each runner. How would you approach creating a table with each runner and their average mins it took for a mile according to all of their race results?

Try the code above to start. You now have a table with a row for each record of a run in every race. The row shows the runner’s name, the distance the race was, the number of minutes it took and how many minutes it took for each mile. What’s the next step to make a table that shows the average minute miles for each runner? To take a step further, how about we even sort it by the runner with the best average to the worst?

This could be done by adding in a simple grouping and sorting with the average as the above code demonstrates.

You can watch a video of a brief walk through of this topic here.