SQL or Structured Query Language is basically a programming language which 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 which is already present in the databases. It is also used for the creation and alteration of new tables. Businesses have thus, relied on SQL for its ability to make sense of huge mounds of data which keeps growing every day. SQL is thus used for having complete control over the databases.
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:
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.
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;
A). expression1, expression2,…expression_n
These are those expressions which are not basically included within the aggregate function and have to be included in the GROUP BY Clause near the end of the SQL statement.
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.
This refers to the column or expression where the aggregate_function will be used on
These are those tables from which you want to retrieve your records. There has to at least one table which 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.
F). HAVING conditions
This is basically 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.
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 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 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.
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
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)<50000;
The Syntax can also be written in a simplified version, as mentioned below:
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.
Let 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
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.
It will be as follows:
Results will be:
In this blog, you have seen in detail the working of the SQL HAVING clause along with many examples to make the terms clearer. The salient features of the HAVING clause have been discussed, and it is clear that HAVING clause if 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 refer 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.
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.
Receive Latest Materials and Offers on SQL Server Course