We use the AVG function to obtain the average value of the specified column. Its one of the most used SQL Server functions.
Let’s make examples for a better understanding of AVG Function.
Example:
First, we create a table with the help of the following script and add a few records into this table.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [TestDB] GO CREATE TABLE [dbo].[ProductTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [ProductCategory] [varchar](250) NOT NULL, [ProductName] [varchar](250) NOT NULL, [ProductPrice] int ) ON [PRIMARY] GO INSERT INTO [dbo].[ProductTable] VALUES ('technology','Samsung NOTE 8',3400), ('kitchenstuff','mixer',430),('technology','LG Television',6700),('kitchenstuff','bakery',9800) |
Let’s calculate the average prices of all products in the ProductTable table using the AVG function:
|
1 |
Select AVG(ProductPrice) AverageProductPrice From [dbo].[ProductTable] |
AVG Function with GROUP BY Clause:
We can also use the AVG function with GROUP BY Clause. For more information about GROUP BY Clause, I suggest you read the article “Group By Clause, SUM, MAX, MIN, AVG, COUNT and COUNT_BIG Functions in SQL Server“.
Let’s group the data by ProductCategory and calculate the average prices of the groups with GROUP BY Clause:
|
1 2 |
Select ProductCategory,AVG(ProductPrice) AveragePricePerGroup From [dbo].[ProductTable] GROUP BY ProductCategory |
AVG Function with OVER Clause:
You can find details in the article “OVER Clause in SQL Server (TSQL)“.
