Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL Server - Get Started
  • Install SQL Server
  • SQL Server Management Studio
  • SQL Server - Windows Authentication
  • SQL Server - Authentication
  • SQL Server - Create New User
  • SQL Server - GRANT/REVOKE Permissions to User
  • SQL Server - Data Types
  • SQL Server - Naming Conventions
  • SQL Server - CREATE Database
  • SQL Server - CREATE Table
  • Add Columns
  • Identity Column
  • Rename Column, Table
  • Drop Columns
  • SQL Server - Schema
  • SQL Server - Tables Relations
  • SQL Server - Primary Keys
  • Modify/Delete Primary Keys
  • SQL Server - Foreign Keys
  • Modify/Delete Foreign Keys
  • SQL Server - Check Constraints
  • SQL Server - Unique Constraints
  • SQL Server - Views
  • Modify/Delete Views
  • SQL Server - Functions
  • SQL Server - Stored Procedures
  • Stored Procedure Parameters
  • SQL Server - Indexes
  • Non-clustered Indexes
  • Modify/Delete Indexes
  • SQL Server - Triggers
  • DDL Triggers
  • LOGON Triggers
  • Enable/Disable Triggers
  • Modify/Delete Triggers
  • SQL Server - Sequence
  • SQL Server - Synonyms
  • SQL Server - IF ELSE Statement
  • SQL Server - Loops
  • SQL Server - Insert Data
  • SQL Server - Update Data
  • SQL Server - Delete Data
  • SQL Server - Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL Server - Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Self Join
  • Dynamic SQL
  • Built-in Functions
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

SQL Server - GROUP BY Clause

In SQL Server, the GROUP BY clause is used to get the summary data based on one or more groups. The groups can be formed on one or more columns. For example, the GROUP BY query will be used to count the number of employees in each department, or to get the department wise total salaries.

You must use the aggregate functions such as COUNT(), MAX(), MIN(), SUM(), AVG(), etc., in the SELECT query. The result of the GROUP BY clause returns a single row for each value of the GROUP BY column.

Syntax:

SELECT column1, column2,...columnN FROM table_name [WHERE] [GROUP BY column1, column2...columnN] [HAVING] [ORDER BY]

The SELECT clause can include columns that are used with the GROUP BY clause. So, to include other columns in the SELECT clause, use the aggregate functions like COUNT(), MAX(), MIN(), SUM(), AVG() with those columns.

GROUP BY Characteristics:
  • The GROUP BY clause is used to form the groups of records.
  • The GROUP BY clause must come after the WHERE clause if present and before the HAVING clause.
  • The GROUP BY clause can include one or more columns to form one or more groups based on that columns.
  • Only the GROUP BY columns can be included in the SELECT clause. To use other columns in the SELECT clause, use the aggregate functions with them.

For the demo purpose, we will use the following Employee and Department tables in all examples.

sample tables

Consider the following GROUP BY query.

SQL Script: GROUP BY
SELECT DeptId, COUNT(EmpId) as 'Number of Employees' FROM Employee GROUP BY DeptId; --following query will return same data as above SELECT DeptId, COUNT(*) as 'No of Employees' FROM Employee GROUP BY DeptId;

The above query includes the GROUP BY DeptId clause, so you can include only DeptId in the SELECT clause. You need to use aggregate functions to include other columns in the SELECT clause, so COUNT(EmpId) is included because we want to count the number of employees in the same DeptId. The 'No of Employees' is an alias of the COUNT(EmpId) column. The query will display the following result.

groupby query

The following query gets the department name instead of DeptId in the result.

SQL Script: GROUP BY
SELECT dept.DeptName as 'Department', count(emp.empid) as 'No of Employees' FROM Employee emp, Department dept WHERE emp.deptid = dept.DeptId GROUP by dept.DeptName
groupby query

In the same way, the following query gets the department-wise total salaries.

SQL Script: GROUP BY
SELECT dept.DeptName, sum(emp.salary) as 'Total Salaries' FROM Employee emp, Department dept WHERE emp.deptid = dept.DeptId GROUP by dept.DeptName
groupby query

The following query would throw an error, because dept.DeptName is not included in the GROUP BY clause, or no aggregate function is used.

SQL Script: GROUP BY
SELECT dept.DeptName, sum(emp.salary) as 'Total Salaries' FROM Employee emp, Department dept WHERE emp.deptid = dept.DeptId GROUP by dept.DeptId
groupby query
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.