Our queries are getting fairly complex, but we have nearly introduced all the important parts of a SELECT query. One thing that you might have noticed is that if the GROUP BY clause is executed after the WHERE clause (which filters the rows which are to be grouped), then how exactly do we filter the grouped rows?

Luckily, SQL allows us to do this by adding an additional HAVING clause which is used specifically with the GROUP BY clause to allow us to filter grouped rows from the result set.

Select query with HAVING constraint

SELECT group_by_column, AGG_FUNC(_column_expression_) AS aggregate_result_alias, … FROM mytable WHERE _condition_ GROUP BY column **HAVING _group_condition_**;

The HAVING clause constraints are written the same way as the WHERE clause constraints, and are applied to the grouped rows. With our examples, this might not seem like a particularly useful construct, but if you imagine data with millions of rows with different properties, being able to apply additional constraints is often necessary to quickly make sense of the data.

Did you know?

If you aren't using the GROUP BY clause, a simple WHERE clause will suffice.

Exercise

For this exercise, you are going to dive deeper into Employee data at the film studio. Think about the different clauses you want to apply for each task.

1. Find the number of Artists in the studio (without a **HAVING** clause)
	1. SELECT COUNT(*) FROM employees WHERE Role = 'Artist';
2. Find the number of Employees of each role in the studio
	1. SELECT Role, Count(*) FROM employees GROUP BY Role;
3. Find the total number of years employed by all Engineers
	1. SELECT Sum(Years_employed) FROM employees WHERE Role = 'Engineer';

Exercise 11 — Tasks

1. Find the number of Artists in the studio (without a **HAVING** clause) ✓
2. Find the number of Employees of each role in the studio ✓
3. Find the total number of years employed by all Engineers ✓

This webpage is a lesson from SQLBolt covering the use of aggregate queries in SQL, specifically focusing on the HAVING clause.

• It explains that the HAVING clause is used to filter grouped rows in a result set, and is written similarly to the WHERE clause, but applies after grouping.

• The lesson provides the SQL query structure for using HAVING:

  SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
  FROM mytable
  WHERE condition
  GROUP BY column
  HAVING group_condition;
  

• The lesson emphasizes that HAVING is valuable for refining large datasets after grouping, whereas if there's no grouping, a simple WHERE clause is enough.

• There is an exercise that uses an Employees table with columns such as Role, Name, Building, and Years Employed. Sample tasks include:

  • Finding the number of Artists in the studio (without using HAVING)
  • Counting employees by role (with grouping)
  • Calculating the total years employed by all Engineers.

The objective is to help users understand when and how to use the HAVING clause for more powerful SQL analytics.[1]

1

Here are some alternative ways to write the query that counts the number of employees whose role is 'Artist':

• Using an alias for the count result: ✅

  SELECT COUNT(*) AS artist_count FROM employees WHERE Role = 'Artist';
  

• Using COUNT on a specific column (Role is not NULL here): ✅

  SELECT COUNT(Role) FROM employees WHERE Role = 'Artist';
  

• Using SUM and a conditional expression (works in MySQL, PostgreSQL, SQLite, not standard SQL): ✅

  SELECT SUM(CASE WHEN Role = 'Artist' THEN 1 ELSE 0 END) FROM employees;
  

• Using GROUP BY and filtering (less common for this task, but possible): ✅

  SELECT Role, COUNT(*) FROM employees GROUP BY Role HAVING Role = 'Artist';
  

All of these queries will give you the count of employees with the role 'Artist', although the last one returns the role along with the count.

1

Here are different ways to write the query that counts employees for each role:

• Using an alias for the count result: ✅

  SELECT Role, COUNT(*) AS count_per_role FROM employees GROUP BY Role;
  

• Using COUNT on a specific column: ✅

  SELECT Role, COUNT(Role) FROM employees GROUP BY Role;
  

• Using ORDER BY to sort results (optional enhancement): ✅

  SELECT Role, COUNT(*) FROM employees GROUP BY Role ORDER BY Role;
  

• Filtering groups with HAVING (e.g., only show roles with more than 1 employee): ✅

  SELECT Role, COUNT(*) FROM employees GROUP BY Role HAVING COUNT(*) > 1;
  

• Getting counts and including roles with zero employees (if you have another table with all possible roles, like roles):

  SELECT r.Role, COUNT(e.EmployeeID)
  FROM roles r
  LEFT JOIN employees e ON r.Role = e.Role
  GROUP BY r.Role;
  

All of these variations provide a grouped count of employees by role, and you can tailor them depending on your requirements.

1

Here are different ways to write the query that gets the total years employed for all Engineers:

• Using an alias for the result: ✅

  SELECT SUM(Years_employed) AS total_years_engineer FROM employees WHERE Role = 'Engineer';
  

• Using SUM with a conditional CASE (works in MySQL, PostgreSQL, SQLite): ✅

  SELECT SUM(CASE WHEN Role = 'Engineer' THEN Years_employed ELSE 0 END) AS total_years_engineer FROM employees;
  

• With GROUP BY to get totals for each role (including Engineer): ✅

  SELECT Role, SUM(Years_employed) FROM employees GROUP BY Role HAVING Role = 'Engineer';
  

• Only engineers, with GROUP BY and alias: ✅

  SELECT Role, SUM(Years_employed) AS total_years FROM employees WHERE Role = 'Engineer' GROUP BY Role;
  

Each of these will give you the total years employed for all employees in the 'Engineer' role, using different approaches and features of SQL.

1