Understand Group by Clause in SQL Server - SQL Server / TSQL Tutorial Part 130

Scenario: 

You are working as SQL Server Developer. You have a dbo.Customer table which has CountryShortName and SaleAmount. You are asked to write a query that should return Sum of SaleAmount , count of records by CountryShortName.


Solution:

Group by clause is often used with aggregate functions such as Sum, Avg,Count,Max,Min to group the result set by column/s.

Let's create our sample table with some data and write our query with Group by to answer our question.

Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)


Let's write our query by using Sum, Count and Group by Clause

SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname 
FROM   dbo.customer 
GROUP  BY countryshortname

How to use Group by Clause in SQL Server


You can also use multiple columns in group by clause. think about if our table would have states and you would like to group by CountryShortName and State, You would simple include State in query as shown below.


SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname,
       [State]
FROM   dbo.customer 
GROUP  BY countryshortname,[State]





Video Demo: What is Group by Clause in SQL Server


3 comments:

  1. Is the a ordering the columns matter in GROUP BY clause?
    Like if we have
    Group by a,b,c
    OR
    Group by c,a,b?
    will it impact on a query result?
    Please assists!

    ReplyDelete
  2. All the Students have to Prepare well and Always aim to Score Pass marks in the All Subjects, For best Preparation Students have to Study This Year HPBOSE Plus Two Question Paper 2023 Links will be useful you Download the Questions 2023 in Those Link you can find your HP 12th Std important Questions 2023 Paper 2023 you have to Study Sincerely to Score high marks. HP Board 12th important Questions 2023 HPBOSE Plus Two Sample Paper 2023 to score High for the Academic year 2023, Students can Complete their Preparation and can also Revise the entire portion by Solving Hindi, English Medium, HP Plus two Model Question Paper 2023.

    ReplyDelete