Learning of SQL Day 17

 

Day 17: HAVING Clause

Introduction:

• The HAVING clause is used to filter groups of rows created by the GROUP BY clause. It is often used with aggregate functions to filter the result set after grouping.

Key Concepts:

• Filtering Groups: Apply conditions to groups created by GROUP BY.

• Aggregate Functions with HAVING: Use aggregate functions to filter groups.

SQL Commands and Examples:

1. Using HAVING Clause:

   • Filter groups based on aggregate function results.

sql

-- Group courses by credits and show only groups with more than 1 course
SELECT Credits, COUNT(*) AS NumberOfCourses
FROM Courses
GROUP BY Credits
HAVING COUNT(*) > 1;

2. Combining WHERE and HAVING Clauses:

   • Use the WHERE clause to filter rows before grouping and the HAVING clause to filter groups after grouping.

sql

-- Select courses with more than 1 credit and group by credits to show only groups with more than 1 course
SELECT Credits, COUNT(*) AS NumberOfCourses
FROM Courses
WHERE Credits > 1
GROUP BY Credits
HAVING COUNT(*) > 1;

3. HAVING with Multiple Conditions:

   • Use multiple conditions in the HAVING clause.

sql

-- Group employees by department and show only departments with more than 2 employees and average salary greater than 50000
SELECT Department, COUNT(*) AS NumberOfEmployees, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 2 AND AVG(Salary) > 50000;

Practice Exercise:

1. Use the Employees table with columns EmployeeID, FirstName, LastName, Department, Salary.

2. Write a query to group employees by Department and filter groups to show only those with more than 1 employee.

3. Write a query to group employees by Department and filter groups to show only those with an average Salary greater than 60000.

4. Write a query to group courses by Credits and filter groups to show only those with a total Credits greater than 10.

sql

-- Group employees by department and show only groups with more than 1 employee
SELECT Department, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;

-- Group employees by department and show only groups with an average salary greater than 60000
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;

-- Group courses by credits and show only groups with a total credits greater than 10
SELECT Credits, SUM(Credits) AS TotalCredits
FROM Courses
GROUP BY Credits
HAVING SUM(Credits) > 10;

Using the HAVING clause effectively allows you to apply conditions to groups, making your data analysis more precise and meaningful.