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