MySQL Lesson 5: More Aggregate Functions, Aliases & Sorting Query Results

Hello everybody,

It’s Michael, and today’s post will be covering more aggregate functions along with aliases and sorting query results (using the same database).

I already covered one aggregate function-COUNT-in the previous post. This time I’ll be covering a few more aggregate functions-AVG, SUM, MAX, MIN. For those unfamiliar, aggregate functions perform a calculation on a set of values and return a single value.

Let’s start with a query using the AVG function.

In this query, I selected the fields Gender and Age from the Singer table and grouped my results by Gender. The AVG(Age) basically calculates the average age of the group of male singers as well as the average age of female singers (results for each gender are displayed separately). As you can see, the average age of female singers on this database is 35.75 years, while the average age for male singers is 42.2143. The AVG function will ignore any null results, so bands/groups and deceased singers didn’t factor into this query, as the Age field is null in those cases.

Let’s try a similar query (by selecting both average age and gender) except using the Featured Artist table this time.

Similar to the previous query, the results are grouped by gender and the average age is calculated for each gender group. In this case, the average age for female featured artists in 38.35 years and for male featured artists it is 41.9304 years. Also, similar to the previous query, null results are ignored, so deceased artists and bands/groups did not factor into the calculation of the results.

However, you may have noticed something different at this portion of the query

• AVG(Featured Artist Age) AS Average Age

The AS keyword signifies an alias, which basically gives the column a different name in order to improve the query’s readability. Aliases are beneficial because to those unfamiliar with MySQL jargon, column names like AVG(Featured Artist Age) aren’t easy to understand due to their technical nature. Thus, column aliases like Average Age make query results understandable for anybody.

• You can also use aliases for table names too.

Now let’s introduce another aggregate function-MAX.

In this query, I selected Genre and Release Date from the Album table. The part of the query with MAX(Release Date) AS Most Recent Album will display the maximum release date (which means the date of the most recently released album) for each genre listed in the table.

However, you may have noticed another new concept in this query-the ORDER BY clause.ORDER BY basically sorts the results in either ascending or descending order (indicated by ASC and DESC respectively). In this query, the dates (and in turn, the genres) are sorted in descending order, which goes from the maximum release date (June 29, 2018) to the farthest back date (September 29, 2009).

• The ORDER BY clause works with both numerical and non-numerical data. If you’re dealing with non-numerical data such as a list of names, ASC and DESC will sort your data in alphabetical and reverse alphabetical order respectively.

Oh, and in case you were wondering which albums correspond to which dates, here’s a rundown

• September 29, 2009-Barbara Streisand-Love is the Answer
• May 23, 2011-Lady Gaga-Born This Way
• November 19, 2012-Rihanna-Unapologetic
• September 13, 2013-Avicii-True
• June 13, 2014-Linkin Park-The Hunting Party
• January 8, 2016-David Bowie-Blackstar
• April 29, 2016-Martina McBride-Reckless
• November 3, 2017-Grace VanderWaal-Just The Beginning
• June 15, 2018-Mike Shinoda-Post Traumatic
• June 29, 2018-Gorrillaz-The Now Now

Now here’s the next aggregate function I will cover-MIN.

In this query, I selected the Album_idAlbum foreign key and Track Duration field from the Tracks table. TheMIN(Track Duration) AS Shortest Song portion selects the duration of the shortest song in each album (only the ID number for the album is displayed). If you’re wondering which duration corresponds to which song, look at the spreadsheet with the Tracks information I provided in MySQL Lesson 4.

Here’s a query with the last of the four aggregate functions I will cover-SUM.

This query is similar to the first one because I chose the Gender and Age fields from the Album table and grouped the results by Gender, except this time I used the SUM function instead of the AVG function. In this query, the sum function displays the total age for male singers and female singers in separate rows (572 for female singers and 591 for male singers). Remember that null records (representing bands and deceased singers) are not factored into the query’s calculations.

Thanks for reading,

Michael