# 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!

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

[sql]

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

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

GROUP BY C.Title

[/sql]

This will return

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
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
GROUP BY C.Title
[/sql]

This is the result

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

#### COUNT()

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

#### SQL GROUP BY SUM()

[sql]
GROUP BY StudentID
[/sql]

#### GROUP BY COUNT() MULTIPLE COLUMNS

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