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
Is the a ordering the columns matter in GROUP BY clause?
ReplyDeleteLike if we have
Group by a,b,c
OR
Group by c,a,b?
will it impact on a query result?
Please assists!
Đặt vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ bao nhiêu
đặt vé máy bay giá rẻ từ mỹ về việt nam
bao giờ có chuyến bay từ đức về việt nam
lịch bay từ moscow đến hà nội
giá vé máy bay từ anh về việt nam
mua vé máy bay giá rẻ từ pháp về việt nam
bảng giá khách sạn cách ly tphcm