Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder is a tool for creating and editing queries visually. Part 4 described how to include native SQL aggregate functions in your queries to display column statistics. This installment describes how to use the Query Builder to filter grouped data based on a HAVING condition.

About the Sakila Sample Database

The query that we'll be building here today will run against the Sakila sample database. It contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.

Filtering Result Groups with the HAVING Clause

The SQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows based on one or more criteria. In contrast to the WHERE clause, which is applied before the GROUP BY clause, the HAVING clause applies a filter to rows AFTER they have been aggregated by the the GROUP BY clause.

Determining How Many Actors Share the Same Last Name

If we wanted to know how many actors in our database share the same last name with at least two other actors, we could use the GROUP BY clause to aggregate actors according to the last_name field of the actors table.

I find that whether I'm constructing a query using the Query Editor or the Query Builder, it's best to choose the tables first.

• With that in mind, open the Query Builder, click on the <Click here to add tables> label beside the FROM keyword, and choose the sakila.actor table from the list:
  


• That will cause the actor table to appear in the top pane along with all of its fields. We will require two fields: the last_name and a count of rows. Click the box beside the last_name field in the table:
  

  To add the Count function to the field list, click on the <Click here to add fields> label underneath the sakila.last_name field in the SQL statement and enter "Count(*)" in the Edit tab of the popup dialog:

• The next step is to add the GROUP BY clause. To do that, click the <Click here to add GROUP BY> label and choose the sakila.last_name field from the popup dialog.


• Click the OK button to close the Query Builder.

That will add the following SQL to the Query Editor:

SELECT
actor.last_name,
Count(*) AS last_name_count
FROM
actor
GROUP BY
actor.last_name

Here are the results produced by the above query:

As you can see, the results are grouped and sorted by last_name. What it doesn't do is limit the results to those actors who share their last name with at least two other actors. To do that we need to add the HAVING clause.

• Reopen the Query Builder and click the <Click here to add conditions > label beside the HAVING keyword. That will insert an "<--> = <-->" expression label.


• Click on the "<--> label on the left-hand side of the expression. The last_name_count field doe not appear in the field list because it only contains table fields. Therefore, enter it in the Edit tab:
  
• Next, click on the equals "=" label to enter the comparison operator. Choose the "greater than or equal to" (>=) operator from the list:
  
• finally, click on the "<--> label on the right-hand side of the expression and enter a value of "3" in the Edit tab.


• Click the OK button to close the Query Builder.

That will add the "HAVING last_name_count >= 3" expression to the query so that, this time, the query only shows actors whose last names appear three or more times in the table: