myITSchools

GROUP BY Statement in SQL Server

The GROUP BY clause in SQL Server is used to arrange rows into groups based on one or more columns. It is often used with aggregate functions such as COUNT, SUM, AVG, MIN, MAX, etc. to generate summary reports. The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The syntax of the GROUP BY clause is:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

For example,

Select Name, Sum (Salary) From Employee   Group by Name; 
Select Name, Sum (Salary) From Employee   Group by Name Having Sum (Salary)>23000;

The GROUP BY clause must come after the WHERE clause (if present) and before the ORDER BY clause. Only the columns listed in the GROUP BY clause can be included in the SELECT clause. Here is an example of a query that uses the GROUP BY clause:

SELECT category, COUNT (*) AS num_products
FROM products
GROUP BY category
ORDER BY num_products DESC;

This query returns the number of products in each category, sorted in descending order by the number of products.

For more information and examples of the GROUP BY clause in SQL Server, you can check out these resources:

For Example,

  1. The following SQL statement lists the number of customers in each country:

SELECT COUNT(CustomerID), Country FROM Customers
GROUP BY Country;

  • The following SQL statement lists the number of customers in each country, sorted high to low:

SELECT COUNT(CustomerID), Country FROM Customers
GROUP BY Country ORDER BY COUNT(CustomerID) DESC;

  • The following SQL statement lists the number of orders sent by each shipper:

SELECT Shippers.ShipperName, COUNT (Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

  •  Group by multiple columns is say, for example, GROUP BY column1, column2. This means placing all the rows with the same values of columns column 1 and column 2 in one group. Consider the below query:
Select Subject, Year, Count (*) From Student
Group by Subject, Year;

More example,

USE Finance;

Select *from [Sales 2023];

————————————————————————–

Select Region, SUM(QTY) As Total_Qty from [Sales 2023] Group by Region;

Select Region, Max (QTY) As Total_Qty from [Sales 2023] Group by Region;

Select Region, Avg (QTY) As Total_Qty from [Sales 2023] Group by Region;

Select Region, Count (QTY) As Total_Qty from [Sales 2023] Group by Region;

—————————————————————————

–Multiple group

Select Region, M_name, count (QTY) As Total_Qty from [Sales 2023] Group by Region, M_name;

Select Product, Category, count (Qty) As Average_Qty from [Sales 2023] Group by Product, Category;

Select Region, Qty, count (Qty) As Total_Qty from [Sales 2023] Group by Region, Qty Having Qty>50;
Scroll to Top