MS SQL Group By Clause | SQL Beginner Guide

MS SQL Group By Clause in SQL is used with aggregate functions to group data based on columns. Before , we jump to details of the Group By clause let’s try to understand about grouping.

SQL is based on set-theory and whenever you’re composing a query you treat a table as a whole & not row-by-row. Similarly , when we use aggregate functions we have to group the sets . This way you will have aggregated view of the set.

What is the Difference Between a Group & Set?

What is a SET in SQL

A set is collection of elements . In our sample database , we have a set of courses with the students who have taken them formed up as a table Studentgrade. See below

What do you see? Collection of different courses right?

But as a whole they’re all courses! Wonderful you’re learning already!

What is a Group in SQL

Group is also a set but it is enclosed under certain conditions. What those conditions might be is entirely up to us. We humans , learn by examples.

Let’s apply a condition on our StudentGrade table to see how many students have taken up a particular course. This we’ll form a Group!

SQL Group By

 

This is just a snapshot of 40 rows of data. Run this code


SELECT C.Title,COUNT([StudentID]) AS TotalStudentsinCourse

FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID

GROUP BY C.Title

This will return SQL Grouping Example

This is what we did here. We have applied a condition on a set of courses & student with their grades & formed a group of Number of students who have taken up a course.

Another way to explain it is this – > Group is based on Course Name & they are grouped together on a condition which is total number of students in a course

So , the difference between two is very simple now .

Set is collection of elements & group is a set based on any condition.

MS SQL Group By Clause

Aggregate functions (SUM () , COUNT(),AVG(),MIN(),MAX() ) requires data to be grouped by Group By Clause based on any one or more than one columns.

MS SQL Group By Clause appears at the end of the SELECT query .

SYNTAX

SELECT aggregatefunctions(columnname1) , columnname2
FROM tablename
GROUP BY columns2

Let’s use the example we have

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

This is the result
SQL Grouping Example

5 Things to Know About MS SQL Group By Clause

  1. In aggregate functions if we use only one column on which aggregation is applied , then it’s not necessary to have a GROUP BY clause . But if you want to introduce another column then it becomes necessary
  2. When you apply Group by clause on a column , all the values in columns will appear only once. Since , they are now in groups. See above example of Courses
  3. All the columns that you will introduce in SELECT query , also will appear in GROUP BY clause .
  4. Grouping will be for all the columns in the group by clause .
  5. Group data should be filter by HAVING clause

We will write about HAVING Clause in next article.

Time to apply some aggregate functions and apply all the theory we have learned today!

GROUP BY EXAMPLES

SQL GROUP BY

COUNT()

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

 

SQL GROUP BY SUM()

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

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

Aggregate functions & Group by functions are responsible when we want to summarise the data for our reports. Both of these concepts are comprehensively use by the ETL developers & query writers to do the reporting. So if you’re looking to be in any of these areas you have to have a great understanding of SETs , Groups & then , understanding of aggregate functions with Group by clause should be a piece of cake.
I hope you have enjoyed this article. Do share on your Social media & subscribe us for the latest articles.

See you with HAVING Clause article next time

Cheers!

Advertisements

Leave a Reply

%d bloggers like this: