How to use Sum, Avg and Count in Select Statement - SQL Server / TSQL Tutorial Part 128

Scenario:

Let's say that you have dbo.Customer table with SaleAmount column and you are asked to write a query that should return you sum of SaleAmount, Average of SaleAmount and Count of all the records.

Solution:

You can use Aggregate functions such as Sum, Avg and count in TSQL to find the answer of your question.

Let's create dbo.Customer Table with sample data so we can use Sum, Avg and Count aggregate functions.

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 run our query to find sum of SaleAmount, Avg of SaleAmount and Count of records.

SELECT Sum(saleamount) AS TotalSale, 
       Avg(saleamount) AS AvgSale, 
       Count(*)        AS SaleRecordCount 
FROM   dbo.customer 





How to use Sum, Avg and Count Aggregate Functions in SQL Server