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. 
 
SQL Having

 

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:

SQL Having



Orders Table:

SQL Having



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;  
  
  

SQL Having


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


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


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;  
  
  

SQL Having




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.

Post a Comment

Previous Post Next Post