Aug 04, 2019 
7.2k
27
DecChristmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL
SQL or Structured Query Language is basically a programming language that is used for communications with the databases. It has found huge acceptance by the businesses for its ability to access and manipulate all the information that is already present in the databases. It is also used for the creation and alteration of new tables. Businesses have thus relied on SQL and SQL Having clause for its ability to make sense of huge mounds of data that keeps growing every day. SQL Having clause is used for having complete control over the databases. You can also check SQL Having vs where clause difference to know more about clauses in SQL. Also, SQL Having with group by clause usage is common by database developers.
Read: How to Prevent SQL Injection Attacks?
Although a simple programming language but SQL is robust, it is endowed with many different commands which enable it to exercise data control in many ways like the insertion of data in the tables of the database, modification of data in the existing tables and even deletion of data from SQL database tables. It can also change the whole database structure by creation, deletion, and even modification of other database objects. It operates on a set of commands for manipulation of data.
SQL Clauses are used with SQL Commands and adhere to strict syntax and functionalities. There are three SQL clauses:
SQL Having Group by clauses can be used together. Also, SQL having vs where clause is a hot topic of discussion and it is necessary to learn their differences to succeed in the database space.
Learn SQL Server in the Easiest Way
Let us take up SQL Having Clause in Detail:
A SQL HAVING clause is primarily used together with the GROUP BY clause for restricting the groups of all the returned rows to only the ones whose condition is true. There are multiple benefits of SQL Having with the group by usage as discussed.
Salient Features:
Read: What Does SQL Stand For? Learn About SQL Queries, Joins & Commands
The Syntax of HAVING clause in SQL is as follows:
SELECT expression1, expression2,…expression_n, aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2,…expression_n
HAVING condition;
Various Arguments or Parameters are as follows:
A). expression1, expression2, expression n
These are those expressions that are not included within the aggregate function and have to be included in the GROUP BY Clause near the end of the SQL statement. Let us see how to use SQL Having with Group by clause together.
Read: A Complete Guide of SSRS Tutorial for Beginner
B). aggregate_function
This refers to the aggregate function like the SUM, COUNT, MIN, MAX or even the AVG functions. These are also referred to as the multiple row functions.
SUM(): It returns the sum or total of every group.COUNT(): It returns the number of rows in every group.AVG(): It returns the mean or average of each group.MIN(): It returns the minimum value of each group.MAX(): It returns the maximum value of each group.C). aggregate_expression
This refers to the column or expression where the aggregate_function will be used on
Read: The Evolution of SQL Server Versions and Editions
D). tables
These are those tables from which you want to retrieve your records. There has to at least one table that is listed in the FROM clause.
E). WHERE conditions
This is optional. These refer to the conditions of the records which have to be selected. It is necessary to learn how SQL Having vs Where clauses should be used within a database program.
F). HAVING conditions
This is a next condition which applies only to the aggregated results for restricting the groups of the returned rows. The groups whose condition is evaluated to be TRUE will only be included in the result set.
Sign up for online SQL training classes today to master SQL fundamental skills.
SQL Server Training & Certification
Let us take some examples considering different Functions.
Herein let us discuss the SQL HAVING clause using the SQL SUM function.
You can make use of the SQL SUM function for returning the name of the department and the total sales which have been made by the associated department. The SQL HAVING clause will allow it to filter the results so that only those departments will be returned which have made sales more than $1000.
The SELECT Department, SUM(sales) AS “Total sales.”
FROM order_details
GROUP BY departments
HAVING SUM(sales)>1000;
Let us now discuss the SQL HAVING clause with the SQL COUNT function
You can make use of the SQL COUNT function for returning the name of the department and even the number of people or employees in the connected or associated department which have a salary of over $25000 per year. The SQL HAVING clause will then filter the results so that only those departments are returned, which have more than ten employees.
Read: How to Increase the Speed of SQL Query Execution
The SELECT department, COUNT (*) AS "Number of employees."
FROM employees
WHERE salary >25000
GROUP BY department
HAVING COUNT (*) >10;
Let us now discuss using the SQL HAVING function with the SQL MIN Function
Read: What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key
You can make use of the SQL MIN function for returning the name of every department along with the minimum salary in the same. The SQL HAVING clause will then return only those departments where the minimum salary is more than $35000.
The SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN (salary) >35000
Lastly, let us now discuss the SQL HAVING function with the SQL MAX function
In this, you can use the SQL MAX function for returning the name of every department and the maximum salary which is present in the department. The SQL HAVING clause thus return only those departments where the maximum salary is less than the $50000.
The SELECT department, MAX(salary) AS "Highest salary."
FROM employees
GROUP BY department
HAVING MAX(salary)The Syntax can also be written in a simplified version, as mentioned below:
Read: How To Quickly Get Entry Level SQL Jobs
The rules of Syntax state that the expressions which are being used in the group by clause should be either containing an aggregate function: COUNT, AVG, SUM, MIN, MAX. It can be one of the grouping expressions.
SELECT column-namesFROM table-nameWHERE conditionGROUP BY column-namesHAVING conditionLet us take some examples to explain the above:
If you have to list the number of some specific customers in every country but you have to include only those countries which have more than ten customers.
It will be as follows:
Results: 3 records
Count Country
11 France
11 Germany
13 USA
In another example, if you have to list the number of customers in every country except the USA, in order high to low. You only have to include those countries which have more than 9 or more customers.
Read: The Database as Part of the System Architecture
It will be as follows:
Results will be:
Count Country
11 France
11 Germany
9 Brazil
Register for a demo class now to learn all about SQL clauses in detail with example.
SQL Server Training & Certification
In this blog, you have seen in detail the working of the SQL HAVING clause along with many examples to make the terms clearer. We have also discussed SQL Having vs Where and SQL Having group by clauses in brief. The salient features of the HAVING clause have been discussed, and it is clear that the HAVING clause is used with the GROUP BY clause unless it can be treated like the WHERE clause. In case you feel an urge to know more, you may prefer to visit us at www.janbasktraining.com and check out our training course on the SQL Server which will equip you with the basics and the best practices in the same to win you the best jobs in the same.
Read: How to Compare MongoDB and DynamoDB?
FaceBook
Twitter
LinkedIn
Pinterest
Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
May 15, 2019
390.6k
390.6k