Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

SQL Having Clause Functions



Introduction

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: Learn SQL Union All Query Operators with Examples

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

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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:

  • SQL having with Group By: HAVING Clause helps to filter the records which work on the summarized GROUP BY results.
  • SQL Having vs Where: HAVING also refers to the group records which are summarized while the WHERE refers to the individual records.
  • SQL Having group by: 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.
  • SQL Having with Group by: 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.
  • SQL Having vs Where: It is possible that the WHERE and HAVING are present in the same

Read: What is the Substring Function in the SQL? Example of SQL Server Substring

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 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: RDBMS Interview Questions

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

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Let us take some examples considering different Functions.

SQL Having with Different Functions

1). 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 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;

2). 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 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: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners

The SELECT department, COUNT (*) AS "Number of employees."
FROM employees
WHERE salary >25000
GROUP BY department
HAVING COUNT (*) >10;

3). MIN function

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

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

The Syntax can also be written in a simplified version, as mentioned below:

Read: How To Differentiate SQL Server JOIN, IN And EXISTS Clause?

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:

SQL Having Clause With Example

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

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: How to Create Table in SQL Server by SQL Query?

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

Register for a demo class now to learn all about SQL clauses in detail with example.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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. 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: RDBMS Interview Questions


    Janbask Training

    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.


Comments

Trending Courses

AWS

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

Upcoming Class

0 day 14 Jul 2020

DevOps

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

Upcoming Class

17 days 31 Jul 2020

Data Science

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

Upcoming Class

2 days 16 Jul 2020

Hadoop

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

Upcoming Class

3 days 17 Jul 2020

Salesforce

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

Upcoming Class

1 day 15 Jul 2020

QA

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

Upcoming Class

10 days 24 Jul 2020

Business Analyst

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

Upcoming Class

0 day 14 Jul 2020

MS SQL Server

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

Upcoming Class

1 day 15 Jul 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

9 days 23 Jul 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

0 day 14 Jul 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

3 days 17 Jul 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

-1 day 13 Jul 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews