Module 2: Group Data with GROUP BY Clause Part 3

Previous Tutorial - Next Tutorial

Now if you need to filter entire groups, you need a filtering option that is evaluated at the group level, unlike the WHERE clause which is evaluated at the row level. So for this T-SQL provides the HAVING clause like the WHERE clause, the HAVING clause uses a predicate or search condition but evaluates the predicate per group as opposed to per row because it acts on the results of the GROUP by clause.

The aggregation function can be used in the HAVING clause predicate because it acts on the result of the GROUP by clause. We can use the aggregation function in the HAVING clause predicate. So this is the general syntax of the HAVING clause you can specify the HAVING clause after the GROUP by clause.

Now as you see in this slide, if I execute this SELECT statement, then it will give me this result. Now let's see how we can use the HAVING clause to find the number of employees who are having the same job title, but the count of the job title should be more than or equal to 3.

Now let's move to SQL Server Management Studio to see this clause in action. As we have already seen that in the employee table, so many employees have the same job title. Now out of this result set, I want to find the number of employees who are having the same job title, but that count should be more than or equal to 3, so to achieve that requirement we can use the HAVING clause. I want to get only those job titles who have count 3 or more than 3. So HAVING clause can be specified after the GROUP by clause.

Now this time when I execute this query I will get only job titles whose count is either 3 or more than 3. Here what we have done is group only job title and filter only groups which are having count greater than or equal to 3, so the HAVING clause filtering only groups that have a count of rows greater than 3.

In the same way, I can also use another aggregate function, for example, SUM, so let's write another query, and this time we referred data from the sales order detail table. So if I execute this query I will get the sum of the line total from the sales order detail. Now out of this result set, I want to filter out those sales order ID which is having the sum of subtotal greater than 120000.

So for that, this is how we can use the HAVING clause with the SUM function. So after Group by I need to specify the having Clause(HAVING SUM (LineTotal) >=120000). If I execute this query then we will get only those sales order IDs from the sales order details table that exit this 120000 of the subtotal.

Now you have a basic understanding of GROUP by clause, how to use the aggregate function with the GROUP by clause and how to filter group data using this HAVING clause. Now in the next slide, we will learn about the TOP clause and see how it helped us to limit the rows returned in a query result to a specified number of rows or a specified number of percentage of rows in SQL Server.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions