Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

SQL Having Clause Functions

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

SQL Clauses are used with SQL Commands and adhere to strict syntax and functionalities. There are three SQL clauses:

  • Group by Clause
  • Having Clause
  • Order by Clause
SQL Server Curriculum

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.

Salient Features:

  • HAVING Clause helps to filter the records which basically work on the summarized GROUP BY results.
  • HAVING also refers to the group records which are summarized while the WHERE refers to the individual records.
  • Only those groups which are up to the mark of the HAVING criteria are actually returned. HAVING actually pointed to a particular search condition for a group or an aggregate.
  • HAVING needs that a GROUP BY clause is actually present. In case you are not using a GROUP BY clause, you can still use a HAVING function like a WHERE clause.
  • It is possible that the WHERE and HAVING are present in the same

General Syntax of SQL HAVING is:

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 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.

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: SQL Fiddle: The Best Resource to Practice SQL online

D). tables

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

SQL Server quiz

SUM function

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;

COUNT function

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;

MIN function

Let us now discuss using the SQL  HAVING function with the SQL MIN Function

Read: How to Create Table in SQL Server by SQL Query?

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

MAX function

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.

  1. SELECT column-names
  2. FROM table-name
  3. WHERE condition
  4. GROUP BY column-names
  5. HAVING condition

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:

  1. SELECT COUNT (Id), Country
  2. FROM Customer
  3. GROUP BY Country
  4. HAVING COUNT (Id)>10

Results: 3 records

Count Country

11          France

11          Germany

13          USA

Read: What is Data Mining SQL? Data Mining SQL Tutorial Guide for Beginner

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:

  1. SELECT  COUNT (Id), Country
  2.  FROM   Customer
  3. WHERE  Country<>USA
  4. GROUP BY Country
  5. HAVING COUNT (Id) >=9
  6. ORDER BY COUNT (Id) DESC

Results will be:

Count Country

11     France

11     Germany

9      Brazil

free SQL Server demo

Conclusion

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.


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews