How to calculate mean in SQL Server

Mean is nothing but the average of the given set of numbers calculated by dividing the sum of all the numbers in the set by the count of numbers in the set. In SQL Server, you can calculate the mean by using the AVG() function. Let us see an example SQL query to calculate the Mean.

Create a Table and Data Set

For this illustration, Let us create a table with 3 sets of data. i.e. There are 3 customers each one has 10 values (Amount) against them.

CREATE TABLE [dbo].[MTB_Statistics](
	[CustomerID] [int] NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[MTB_Statistics] VALUES
	(1, 100), (1, 200), (1, 300), (1, 400), (1, 500), 
	(1, 600), (1,700), (1, 800), (1, 900), (1, 1000),
	(2, 10), (2, 20), (2, 30), (2, 40), (2, 50), 
	(2, 60), (2,70), (2, 80), (2, 90), (2, 100),
	(3, 4), (3, 1), (3, 6), (3, 2), (3, 6), (3, 5), 
	(3, 14), (3, 12), (3, 18), (3, 7)
GO

Calculate Mean

Now, let us find the mean amount for each customer using the AVG function.

SELECT 
	CustomerID, 
	AVG(Amount) AS [Mean], 
	SUM(Amount) AS [Total Amount]
	FROM [dbo].[MTB_Statistics]
	GROUP BY CustomerID
GO
calculate mean in SQL Server

Related Articles

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.