SQL Server HAVING Clause: A Complete Guide with Examples and Exercises

SQL Server HAVING clause is a powerful tool for filtering the results of a query in an efficient and effective manner. In this article, Novatesting will delve into the basics of HAVING clause and explore how it can be used in combination with SELECT, GROUP BY and aggregate functions to get the desired output. With the help of examples and exercises, we will guide you through the process of mastering the SQL Server HAVING clause.

I. Introduction to SQL Server HAVING clause

The HAVING clause in SQL Server is a filter that is used in conjunction with the SELECT, GROUP BY and aggregate functions to restrict the output of a query. It operates on the groups formed by the GROUP BY clause and returns only those groups that meet certain conditions.

II. SQL Server HAVING examples

1. SQL Server HAVING with the COUNT function example

Let’s say we have a table named “Orders” with columns “OrderID”, “CustomerID”, and “OrderDate”. To find the number of orders made by each customer, we can use the following query:

sql

SELECT CustomerID, COUNT(OrderID) as OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;

This query returns only those customers who have made more than 5 orders.

2. SQL Server HAVING clause with the SUM() function example

To find the total value of orders for each customer, we can use the following query:

sql

SELECT CustomerID, SUM(OrderValue) as TotalOrders
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderValue) > 1000;

This query returns only those customers who have spent more than 1000 in total on orders.

III. Exercises:

1. Exercise 1: Find the number of products sold by each category, where the number of products sold is greater than 100.

sql

SELECT Category, COUNT(ProductID) as ProductsSold
FROM Products
GROUP BY Category
HAVING COUNT(ProductID) > 100;

2. Exercise 2: Find the total sales made by each employee, where the total sales are greater than $10,000.

sql

SELECT EmployeeID, SUM(SaleValue) as TotalSales
FROM Sales
GROUP BY EmployeeID
HAVING SUM(SaleValue) > 10000;

The exercises provided in this article are designed to help you practice and reinforce your learning, and with time and practice, you will become an expert in using the SQL Server HAVING clause.