Aug 04, 2019 
7.1k
29
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - 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: A Comprehensive SQL Server Tutorial Guide for Beginners & Experienced
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: Future Growth of a SQL Server Developer
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: How to Get Your Career as an SSIS Developer Rolling?
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 use SQL Count() Aggregate Function
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: Difference Between SQLite and MySQL
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: Windows SQL Function-All you Need to Know
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: Learn SQL Union All Query Operators with Examples
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
Top 50 DB2 Interview Questions and Answers for Freshers & Experienced
13.3k
Top 50 Informatica interview questions you should prepare
9.5k
DDL, DML, DCL, TCL & DQL -- The Complete SQL Commands Tutorial
12k
What is SQL Formatter? Features of SQL Formatter Online
528.6k
How To Differentiate SQL Server JOIN, IN And EXISTS Clause?
787.7k
Receive Latest Materials and Offers on SQL Server Course
Interviews
Apr 19, 2024
13.3k
