In this tutorial, we are going to learn how to use SQL having clause to details table and query into
group and apply aggregate function in having clause.
The HAVING clause is used with the GROUP BY clause on the select statement query.
Without a GROUP BY clause has having clause can't use.
Having clause behave like the where clause.
SQL HAVING Clause Syntax:
SELECT column_names from table
WHERE condition
GROUP BY column_names
HAVING condition
you will see the having clause appears after the group by clause.
SQL HAVING vs WHERE Clause:
The WHERE clause applies the condition to filter individual rows before groups by clause. The GROUP BY clause details the row s into matches groups. However, the HAVING clauses apply the condition to the groups after the rows are grouped into groups.
SELECT column_names
FROM table
WHERE condition
GROUP BY column_names
HAVING condition
ORDER BY column_names
Here, the important thing is having clause is applied after the group by clause and then the where clause applied before the group by clause.
Here, I have created two tables for customer and orders. The two tables are having a primary key and combined columns are cust_id.
| Customer |
|---|
| cust_id (primary key) |
| cust_name |
| city |
| pincode |
| states |
| phones |
| Orders |
|---|
| order_id (primary key) |
| order_date |
| order_no |
| cust_id |
| Total Amount |
Customer Table:
Orders Table:
SQL Having Clause Example:
I use the customer to group by the states and use the COUNT function to count the cust_id.
SELECT states, COUNT(cust_id) AS Customers
FROM Customer
GROUP BY states
HAVING COUNT(cust_id) >= 2;
Having vs Where:
SELECT states, COUNT(cust_id) AS Customers
FROM Customer
WHERE states <> 'TamilNadu'
GROUP BY states
HAVING COUNT(cust_id) >= 2
ORDER BY COUNT(cust_id) DESC;
SQL Having Inner Join:
The customer and orders table is used to select the customer states and use the COUNT function to count cust_id and As function to display a number of orders and get from customer to use inner join the orders table equal to customer and orders by id Group By customer states and use having clause count cust_id greater than equal to 2.
select customer.states, count(customer.cust_id) As numberoforders
from (customer
Inner join orders on orders.order_id = customer.cust_id)
group by states
having count(customer.cust_id)>=2;
SQL Having Join:
The customer and orders table is used to select the customer name (cust_name) and we are using the AVG function to average the total_amount of orders and group by customer name (cust_name) and Having clause used to display AVG total_amount between 200 to 1200.
select cust_name ,AVG(orders.total_amount) as averageamount
From (orders
JOIN customer c on c.cust_id = orders.order_id)
GROUP BY cust_name
HAVING AVG(total_amount) between 200 and 1200;
I hope you will learn how to create SQL having a clause and you will understand the example. If you have any questions comment on us. I will soon reply or create an article.