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

[sql]

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

[/sql]

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

[sql]
SELECT aggregatefunctions(columnname1) , columnname2
FROM tablename
GROUP BY columns2
[/sql]

Let’s use the example we have

[sql]
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
[/sql]

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

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

 

SQL GROUP BY SUM()

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

GROUP BY COUNT() MULTIPLE COLUMNS

[sql]
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
[/sql]

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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s