EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL having

MySQL having

By Payal UdhaniPayal Udhani

MySQL having

Introduction to MySQL having

In real-time applications, we often need to generate complex reports from the data saved in the database and retrieve the filtered records to display the report to the user. For this, your SQL query must be optimized and correct so that the application’s performance is not hampered even if a large amount of data is present in the tables. The SELECT query constructed for such reports needs to use the functionalities provided in MySQL.

Having a clause is one such functionality that helps apply the expressions’ filters. These expressions can consist of a single column, multiple columns, or even conditions applied to the grouped aggregated data retrieved using the GROUP BY clause. In this article, we will learn about the syntax of the HAVING clause, its evaluation order while execution, and study some examples to get a grip on the usage of the HAVING clause in your queries for retrieval of data.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

The following SELECT query shows the syntax and placement of the HAVING clause in it –

SELECT
list_of_expressions
FROM
name_of_table
WHERE
restrictions_and_conditions
GROUP BY
expressions_for_grouping
HAVING
condition_or_filter_on_grouped_expressions;

In the above syntax –

  • list_of_expressions – The comma-separated list of the columns and the other expressions, such as aggregated values or manipulated values such as product, etc., that the query needs to retrieve from the table data.
  • name_of_table – The table’s name resides in your database and from where the summarized data needs to be retrieved for your use case.
  • restrictions_and_conditions – These are the conditions you can specify on the table columns that need to be fulfilled while retrieving the data.
  • expressions_for_grouping – The resultset retrieved from the query structure above the GROUP BY clause can be summarized and grouped based on certain expressions, including columns and aggregated values of columns as the expressions_for_grouping.
  • condition_or_filter_on_grouped_expressions – The WHERE clause only applies restrictions on the individual records or row of the column. We can use the HAVING clause to apply filters and restrictions and specify conditions on the grouped expressions of the resultset.

In the case of the HAVING clause, the restriction is applied to the grouped values retrieved instead of a single row of the table. When we do not use the GROUP BY clause in the SELECT query, the HAVING clause behaves in the same manner as that of the WHERE clause and applies restriction on individual row-level of the table as grouping expression is absent. If the filter condition specified in the HAVING clause evaluates to true, the grouped record is included in the final resultset based on the GROUP BY clause. Conversely, if the condition evaluates to false, the record is excluded.

Evaluation order:

The evaluation order followed by standard SQL is different than that of MySQL. Evaluation order determines which and when clause will be considered for execution when the SELECT query contains multiple clauses. The diagram below illustrates the order in which MySQL evaluates the clauses of the SELECT query.

Here, we observe that the evaluation of the HAVING clause takes place after the FROM, WHERE, SELECT, and GROUP BY clauses, but before the LIMIT and ORDER BY clauses. However, it is important to note that in the SQL standard, the evaluation order differs. In this case, the execution of the HAVING clause occurs after the GROUP BY clause and before the SELECT clause.

MySQL having 1

Example of MySQL having

For example, we will create a table named educba_articles that will contain the details about the articles, such as id, name, author, rate, pages, month, and status.

CREATE TABLE educba_articles (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
articlename varchar(10) NOT NULL,
author varchar(10) NOT NULL,
rate decimal(5,2) DEFAULT NULL,
month varchar(10) NOT NULL,
status varchar(10) NOT NULL,
pages INTEGER DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

that gives the following output after execution –

MySQL having 2

Now, we will insert some records in it –

INSERT INTO `educba_articles` VALUES
(1, 'sp', 'Payal', 125.65, 'May','Submitted' ,3),
(2,'having', 'Vyankatesh', 326.22, 'June','Pending' ,2),
(3,'something', 'Omprakash', 123.22, 'July','Approved' ,2),
(4, 'anything','sakshi', 645.54 ,'May','Pending' ,3),
(5, 'everything','prerna', 356.54 ,'June','Submitted' ,4),
(6,'events', 'Vyankatesh', 326.22, 'June','Approved' ,2),
(7,'group by', 'Omprakash', 123.22, 'July','Approved' ,2),
(8, 'from','sakshi', 645.54, 'May','Pending' ,3),
(9, 'where','prerna', 356.54 ,'June','Submitted' ,4),
(10, 'limit ', 'Payal', 125.65, 'May','Submitted' ,3),
(11, 'coalesce ', 'Payal', 125.65, 'May','Submitted' ,3),
(12,'order by', 'Vyankatesh', 326.22, 'June','Approved' ,2),
(13,'datatypes', 'Omprakash', 123.22, 'July','Approved' ,2),
(14, 'varchar','sakshi', 645.54 ,'May','Pending' ,3),
(15, 'integer','prerna', 356.54 ,'June','Approved' ,4),
(16,'date', 'Vyankatesh', 326.22, 'June','Pending' ,2),
(17,'now', 'Omprakash', 123.22, 'July','Approved' ,2),
(18, 'curdate','sakshi', 645.54 ,'May','Pending' ,3),
(19, 'not null','prerna', 356.54, 'June','Pending' ,4);

that provides the following result after execution –

Insert record 3

Suppose we have to calculate the total payment for each article for each month and retrieve only those records whose income for the month exceeds 2000. For this, the total amount of article will be rate into pages and then for retrieving the total amount for a particular article for a particular month, we will have to group our result set based on the author and month column and then for retrieving the result whose payment exceeds 2000 amount, we will apply the restriction on our calculated amount in the having clause. Our query statement will be as follows –

SELECT author, MONTH, SUM(rate*pages) AS payment
FROM educba_articles
GROUP BY author, MONTH
HAVING payment > 2000;

that gives the following output –

MySQL having 4

Let’s consider another example where our goal is to retrieve the total payment that we need to pay to each author. In this case, the total payment for all months should exceed 5000. To achieve this, we need to group the data by author, calculate the payment by multiplying the rate and pages, and aggregate this value using the SUM() function to calculate the total payment for each author. Additionally, we should apply a restriction on the calculated value within the HAVING clause. Our query statement will look like follows –

SELECT author, SUM(rate*pages) AS payment
FROM educba_articles
GROUP BY author;
HAVING payment > 5000;

that gives the following output after execution –

outtput

Conclusion

HAVING clause can be used to apply restrictions and filters on the grouped expressions in complex queries that are generally used for reporting purposes using the SELECT query statement in MySQL.

Recommended Articles

We hope that this EDUCBA information on “MySQL having” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Data Type
  2. Split in MySQL
  3. MySQL EXISTS
  4. MySQL WEEKDAY
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more