MS SQL Having Clause | SQL Beginner Guide

Filtering the Groups in MS SQL ( Transact SQL-TSQL)  requires MS SQL Having clause . We discussed in detail about sets & groups in our post on Group by clause.   In order to be able to practice the examples that we will discuss , i recommend you to use our sample database. It’s a script you just have to copy paste in your MS SQL Server Management Studio & everything will be ready.

Also see : How to Install MS SQL Server Management Studio 

MS SQL Having Clause

MS SQL Having clause is used to apply a filter on groups that are formed as a result of group by clause.

SYNTAX


SELECT aggregatefunction(columname), columname2

FROM tablename

GROUP BY columnname2

HAVING condition

These four are mandatory steps to follow for MS SQL Having clause to work. You can use ORDER BY & WHERE as well if needed be.

Before we jump to the examples , I want to clear one more confusion here which is one of the biggest mistake people make while working with MS SQL Group by and MS SQL Having group clauses.

MS SQL Having VS Where

While working with groups you may often come across scenarios where you have to decide between where & having clause. Understanding what they do will help you to write a sound query .

Where is used when we want to apply filtering on a data set or simply when we want to use them on a simple data table. For example , I want to see how many courses are studied by any particular student or all students. Let those Students have Student ID (7,8,9) .See below


SELECT CourseID
,[StudentID]

FROM [School].[dbo].[StudentGrade]
WHERE [StudentID] in (7,8,9)

This is the data without grouping . Data looks like this now

Where example

Now time to apply grouping. My requirement was that I needed to see how many courses are studied by any particular student or all students.  You can see that StudentID 7 & 8 has two rows while 9 has only one.

Here you can count them but imagine you have a data set of thousands of rows. Counting them won’t be an option. So Let’s apply aggregate function to make our life easier.


SELECT COUNT([CourseID]) CourseID
,[StudentID]

FROM [School].[dbo].[StudentGrade]
WHERE [StudentID] in (7,8,9)
GROUP BY [StudentID]

This gives us

Grouping SQL

Much easier to count now right?

So this completes my requirement of how many courses are studied by any particular student or all students! Now comes the interesting part ! Are you ready?

My next requirement is to find the students who are studying 2 courses . Let’s see again the original data

Where example

Can you use a where clause? try & you will find the error saying An aggregate may not appear in the WHERE clause….. because you cannot use aggregated functions with WHERE & you certainly cannot apply a WHERE clause on GROUPED data.

Write this query now


SELECT COUNT([CourseID]) CourseCount
,[StudentID]
FROM [School].[dbo].[StudentGrade]
WHERE [StudentID] in (7,8,9)
GROUP BY [StudentID]
HAVING COUNT([CourseID])=2

 

Her’s how it works . See CourseCount was a column which was grouped so applying a Where clause on it wasn’t possible so we used HAVING Clause.

Where is working on StudentID because that is not a grouped column .

So the difference between where & having is simple . Where is applied use for simple set & Having is used with grouping.

MS SQL Group By & Having Clause In SQL

COUNT()

SELECT COUNT (Title) AS TotalCourses , CourseID
FROM [Course]
GROUP BY CourseID
HAVING COUNT(CourseID)=1 ;

SQL GROUP BY SUM()

SELECT SUM([Grade]) As TotalGrades , StudentID
FROM [StudentGrade]
GROUP BY StudentID
HAVING SUM(Grade);

GROUP BY COUNT() MULTIPLE COLUMNS

SELECT C.Title,COUNT([StudentID]) AS TotalStudentsinCourse ,DepartmentID
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
GROUP BY C.Title ,DepartmentID
HAVING COUNT([StudentID]);

So in last three of our articles we have learned what is aggregated data , about SQL Server functions on aggregation and how to apply filtering groups in SQL.

Also see

  1. SQL Server Aggregate Functions 
  2. Groups in SQL 

I have received a request from one of our readers about how grouping works in actual. Very interesting article is on its way! See you next time with our first Question & Answers post.

Advertisements

Author: Awais

Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows ! Happy Reading and Learning!

Leave a Reply