Jan 8, 2015

AVG() function in SQL Server

AVG function returns average of the values of column in 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 group 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 column with 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.