PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
having
The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
The MySQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.
The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition. If you omit the GROUP BY clause, the HAVING clause behaves like the WHERE clause.
The Having is also used for filtering the data just like the where clause. it filters the rows from the intermediate result set that is built by using the FROM, WHERE, or GROUP BY clauses in a SELECT statement.
The Having Clause in MySQL is typically used with a GROUP BY clause.
SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;
MySQL evaluates the HAVING clause after the FROM, WHERE, SELECT and GROUP BY clauses and before ORDER BY, and LIMIT clauses:
File Name :
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach*quantityOrdered) AS total
FROM
orderdetails
GROUP BY
ordernumber
HAVING
total > 1000;
Why do we need Having Clause in MySQL?
The WHERE clause could not be used with aggregate data rows, hence the HAVING clause was added to SQL. The purpose of the HAVING clause is to filter the group rows produced by the GROUP BY clause. The HAVING clause is the same as the WHERE clause. The only difference is WHERE clause FILTERS the intermediate data results, while the HAVING clause operates on group rows. Likewise, WHERE clause, we can use conditions and operators with the HAVING clauses to build complex SQL statements.
aggregate_function: It can be any of the aggregate functions such as SUM, COUNT, MIN, MAX, or AVG.
expression1, expression2, expression_n: The expressions which are not encapsulated within an aggregate function must be included in the GROUP BY clause.
Where Conditions: It is optional. If you want to retrieve selected records based on some conditions then you need to specify the conditions using the Where clause.
HAVING having_condition: The Having Clause Condition is used to add a further filter condition that can be applied only to the aggregated results to restrict the number of groups to be returned.
Example :- Filtering rows using WHERE clause
SELECT City, SUM(Salary) as Total_Salary
FROM Employee
WHERE City = 'India'
GROUP BY City;
Example :- Filtering groups using the HAVING clause
SELECT City, SUM(Salary) as Total_Salary
FROM Employee
GROUP BY City
HAVING City = 'India';
Example :- Using both Having and Where Clause in MySQL
SELECT City, SUM(Salary) as Total_Salary
FROM Employee
WHERE Gender = 'Male'
GROUP BY City
HAVING City = 'India';
Example :-
SELECT Department, AVG(Salary) AS 'Average salary'
FROM Employee
GROUP BY Department
HAVING AVG(Salary) > 50000;
SUM function
SELECT product, SUM(quantity) AS "Total quantity"
FROM order_details
GROUP BY product
HAVING SUM(quantity) > 10;
COUNT function
SELECT product, COUNT(*) AS "Number of orders"
FROM order_details
WHERE category = 'produce'
GROUP BY product
HAVING COUNT(*) > 20;
MIN function
SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) < 50000;
MAX function
SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) > 25000;
File Name :
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
File Name :
File Name :
File Name :
File Name :
File Name :
File Name :
File Name :
File Name :
File Name :