MySQL Lesson 4: GROUP BY, HAVING & COUNT

Hello everybody,

Now that I’ve run through the basics of querying, I figured the next functions to work with should be the GROUP BY, HAVING, and COUNT functions. I will cover all three of these functions in this post because I feel they perfectly compliment one another (and you can use all three in the same query).

I’ll begin with a simple GROUP BY query.

As you can see here, I selected the Genre column from the Album table and asked the query to group by Genre. What the query does is group each of the rows by genre; multiple rows with the same genre are grouped together.

Here’s another GROUP BY query, this time using the Singer table.

The results of this query are grouped by Gender (F for female, M for male, and NULL for bands). Multiple rows with the same gender are grouped together.

Now let’s run the first query again with the addition of the COUNT function.

This basically does the same thing as the first query, except it displays how many times each genre appears in the table (that’s the point of including COUNT(Genre)). As you can see, Pop appears the most times (16 of the 57 albums), followed by Rap (15 albums), and Rock (10 albums).

Here’s the second query, also with the addition of the COUNT function

Just like the second query, the results are grouped by gender, except this time the query also counts how many times each gender appears in the table. In other words, this query shows how many female and male singers are listed on this table (17 and 21 respectively). As for why 0 is listed for NULL, MySQL didn’t count the NULL rows in the Gender column (in case you were wondering, there are 10 NULL columns-or 10 bands-listed on this table).

Now let’s run the third query again, this time with the addition of the HAVING function.

The HAVING function is essentially an if statement; it tells the query only select those results that meet certain criteria. In this case, the query will only select genres with COUNT(Genre) greater than 5, meaning that the genre appears more than 5 times on the table. In this case, pop, rap, and rock meet the criteria (appearing 16, 15, and 10 times respectively)

• Syntax tip to keep in mind-HAVING always comes after GROUP BY.

Let’s run the fourth query again, this time with the addition of the COUNT function.

Just like the query above, only results that meet a certain criteria are selected. In this case, we only want the results where Gender = ‘F’ (meaning how many female singers are listed on this table). As you can see, there are 17 female singers listed.

• HAVING doesn’t just need to include numerical criteria (such as COUNT(column_name)) but can also include non-numerical criteria as well (as can be seen above)

By the way, here’s the Excel workbook with all of the spreadsheets that contain the information used in this database-MySQL database (blog). I tried uploading the SQL file but was unable to do so.

Thanks for reading,

Michael