AVG function
returns average of the values of a column in the group.
AVG() Syntax
SELECT AVG(<column_name>)
FROM <table_name>
Example: Suppose we have a table blog as below. Here we
have to fetch AVG() of PageViewed and SessionInMin column for different groups
like title, date, etc..
CREATE TABLE #BLOG
(
ID INT IDENTITY(1,1),
Title VARCHAR(100),
PageViewed INT,
SessionInMin DECIMAL(18,2),
vDate DATETIME
)
INSERT INTO #BLOG (Title,PageViewed,SessionInMin,vDate)
SELECT 'SQL Server overview',20,2.5,'2015/05/05'
UNION ALL
SELECT 'MongoDB Overview',10,4.5,'2015/05/05'
UNION ALL
SELECT 'SSIS Tutorial',12,15.5,'2015/05/05'
UNION ALL
SELECT 'MongoDB Overview',15,6.5,'2015/05/06'
UNION ALL
SELECT 'SSIS Tutorial',30,17.5,'2015/05/06'
SELECT AVG(PageViewed) PageViewed,AVG(SessionInMin) SessionInMin FROM #BLOG
DROP TABLE #BLOG
Average of PageViewd and SessionInMin
column without group
SELECT AVG(PageViewed) PageViewed,AVG(SessionInMin) SessionInMin FROM #BLOG
Result
PageViewed
SessionInMin
----------- --------------
17
9.300000
Average of PageViewd and SessionInMin
column with group of Title column
SELECT AVG(PageViewed) PageViewed,AVG(SessionInMin) SessionInMin,Title FROM #BLOG
GROUP BY Title
Result
PageViewed
SessionInMin Title
----------- --------------- --------
12
5.500000 MongoDB Overview
20
2.500000 SQL Server
overview
21
16.500000 SSIS Tutorial
Average of PageViewd and SessionInMin
a column with a group of vDate column
SELECT AVG(PageViewed) PageViewed,AVG(SessionInMin) SessionInMin,vDate FROM #BLOG GROUP BY vDate
Result
PageViewed
SessionInMin vDate
----------- --------------------- -----------------------
14 7.500000 2015-05-05 00:00:00.000
22 12.000000 2015-05-06 00:00:00.000
Note: 1- NULL value are ignored.
2- Column type should be int, decimal, float,
bigint etc.
