Good Friday Sale : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- 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: How Long Does It Take To Become A SQL developer?

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: SSRS Sub Reports and deployment process-How to do it

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: Microsoft Power BI Certification Exam- 2024 Updated Guidelines

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

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: SAS Tutorial Guide for Beginners

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: What is Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key

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: SQL Server Reporting Service: All You Need to Know about Parameterized Reports

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    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.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

-0 day 29 Mar 2024

QA Course

QA

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

Upcoming Class

-0 day 29 Mar 2024

Salesforce Course

Salesforce

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

Upcoming Class

7 days 05 Apr 2024

Business Analyst Course

Business Analyst

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

Upcoming Class

-0 day 29 Mar 2024

MS SQL Server Course

MS SQL Server

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

Upcoming Class

7 days 05 Apr 2024

Data Science Course

Data Science

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

Upcoming Class

-0 day 29 Mar 2024

DevOps Course

DevOps

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

Upcoming Class

7 days 05 Apr 2024

Hadoop Course

Hadoop

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

Upcoming Class

-0 day 29 Mar 2024

Python Course

Python

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

Upcoming Class

7 days 05 Apr 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

8 days 06 Apr 2024

Machine Learning Course

Machine Learning

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

Upcoming Class

21 days 19 Apr 2024

 Tableau Course

Tableau

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

Upcoming Class

7 days 05 Apr 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews