# SQL Server average deviation function

AVEDEV

Updated: 28 February 2011

Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.

Use the aggregate AVEDEV to calculate the average of the absolute deviations of data points from their mean. The equation for average deviation is

Syntax

Arguments
@x
the values to be used in the AVEDEV calculation. @x is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
·         AVEDEV is an aggregate function and follows the same conventions as all other AGGREGATE functions in SQL Server.
·         If you have previously used the AVDEV scalar function, the AVEDEV aggregate has a different syntax. The AVEDEV scalar function is no longer available in XLeratorDB/statistics2008.
Examples
In this example we have loaded monthly sales figures from 4 regions for the last 3 years into a derived table (n).
In this example, we calculate the average deviation for the entire population.
SELECT wct.AVEDEV(sales) AVEDEV
FROM (VALUES
(1,2008,'East',10000),(2,2008,'East',9500),(3,2008,'East',11000),
(4,2008,'East',11500),(5,2008,'East',12000),(6,2008,'East',12750),
(7,2008,'East',11900),(8,2008,'East',11600),(9,2008,'East',12100),
(10,2008,'East',13000),(11,2008,'East',15000),(12,2008,'East',16000),
(1,2008,'South',11000),(2,2008,'South',10450),(3,2008,'South',12100),
(4,2008,'South',12650),(5,2008,'South',13200),(6,2008,'South',14025),
(7,2008,'South',13090),(8,2008,'South',12760),(9,2008,'South',13310),
(10,2008,'South',14300),(11,2008,'South',16500),(12,2008,'South',17600),
(1,2008,'North',8500),(2,2008,'North',8075),(3,2008,'North',9350),
(4,2008,'North',9775),(5,2008,'North',10200),(6,2008,'North',10838),
(7,2008,'North',10115),(8,2008,'North',9860),(9,2008,'North',10285),
(10,2008,'North',11050),(11,2008,'North',12750),(12,2008,'North',13600),
(1,2008,'West',13000),(2,2008,'West',12350),(3,2008,'West',14300),
(4,2008,'West',14950),(5,2008,'West',15600),(6,2008,'West',16575),
(7,2008,'West',15470),(8,2008,'West',15080),(9,2008,'West',15730),
(10,2008,'West',16900),(11,2008,'West',19500),(12,2008,'West',20800),
(1,2009,'East',10500),(2,2009,'East',9975),(3,2009,'East',11550),
(4,2009,'East',12075),(5,2009,'East',12600),(6,2009,'East',13388),
(7,2009,'East',12495),(8,2009,'East',12180),(9,2009,'East',12705),
(10,2009,'East',13650),(11,2009,'East',15750),(12,2009,'East',16800),
(1,2009,'South',11550),(2,2009,'South',10973),(3,2009,'South',12705),
(4,2009,'South',13283),(5,2009,'South',13860),(6,2009,'South',14726),
(7,2009,'South',13745),(8,2009,'South',13398),(9,2009,'South',13976),
(10,2009,'South',15015),(11,2009,'South',17325),(12,2009,'South',18480),
(1,2009,'North',8925),(2,2009,'North',8479),(3,2009,'North',9818),
(4,2009,'North',10264),(5,2009,'North',10710),(6,2009,'North',11379),
(7,2009,'North',10621),(8,2009,'North',10353),(9,2009,'North',10799),
(10,2009,'North',11603),(11,2009,'North',13388),(12,2009,'North',14280),
(1,2009,'West',13650),(2,2009,'West',12968),(3,2009,'West',15015),
(4,2009,'West',15698),(5,2009,'West',16380),(6,2009,'West',17404),
(7,2009,'West',16244),(8,2009,'West',15834),(9,2009,'West',16517),
(10,2009,'West',17745),(11,2009,'West',20475),(12,2009,'West',21840),
(1,2010,'East',11025),(2,2010,'East',10474),(3,2010,'East',12128),
(4,2010,'East',12679),(5,2010,'East',13230),(6,2010,'East',14057),
(7,2010,'East',13120),(8,2010,'East',12789),(9,2010,'East',13340),
(10,2010,'East',14333),(11,2010,'East',16538),(12,2010,'East',17640),
(1,2010,'South',12128),(2,2010,'South',11521),(3,2010,'South',13340),
(4,2010,'South',13947),(5,2010,'South',14553),(6,2010,'South',15463),
(7,2010,'South',14432),(8,2010,'South',14068),(9,2010,'South',14674),
(10,2010,'South',15766),(11,2010,'South',18191),(12,2010,'South',19404),
(1,2010,'North',9371),(2,2010,'North',8903),(3,2010,'North',10308),
(4,2010,'North',10777),(5,2010,'North',11246),(6,2010,'North',11948),
(7,2010,'North',11152),(8,2010,'North',10871),(9,2010,'North',11339),
(10,2010,'North',12183),(11,2010,'North',14057),(12,2010,'North',14994),
(1,2010,'West',14333),(2,2010,'West',13616),(3,2010,'West',15766),
(4,2010,'West',16482),(5,2010,'West',17199),(6,2010,'West',18274),
(7,2010,'West',17056),(8,2010,'West',16626),(9,2010,'West',17342),
(10,2010,'West',18632),(11,2010,'West',21499),(12,2010,'West',22932)
)n(mth,yr,region,sales)

This produces the following result.

AVEDEV
----------------------
2354.4212962963

(1 row(s) affected)

In this example, we calculate the average deviation for each year.
SELECT yr,
wct.AVEDEV(sales) AVEDEV
FROM (VALUES
(1,2008,'East',10000),(2,2008,'East',9500),(3,2008,'East',11000),
(4,2008,'East',11500),(5,2008,'East',12000),(6,2008,'East',12750),
(7,2008,'East',11900),(8,2008,'East',11600),(9,2008,'East',12100),
(10,2008,'East',13000),(11,2008,'East',15000),(12,2008,'East',16000),
(1,2008,'South',11000),(2,2008,'South',10450),(3,2008,'South',12100),
(4,2008,'South',12650),(5,2008,'South',13200),(6,2008,'South',14025),
(7,2008,'South',13090),(8,2008,'South',12760),(9,2008,'South',13310),
(10,2008,'South',14300),(11,2008,'South',16500),(12,2008,'South',17600),
(1,2008,'North',8500),(2,2008,'North',8075),(3,2008,'North',9350),
(4,2008,'North',9775),(5,2008,'North',10200),(6,2008,'North',10838),
(7,2008,'North',10115),(8,2008,'North',9860),(9,2008,'North',10285),
(10,2008,'North',11050),(11,2008,'North',12750),(12,2008,'North',13600),
(1,2008,'West',13000),(2,2008,'West',12350),(3,2008,'West',14300),
(4,2008,'West',14950),(5,2008,'West',15600),(6,2008,'West',16575),
(7,2008,'West',15470),(8,2008,'West',15080),(9,2008,'West',15730),
(10,2008,'West',16900),(11,2008,'West',19500),(12,2008,'West',20800),
(1,2009,'East',10500),(2,2009,'East',9975),(3,2009,'East',11550),
(4,2009,'East',12075),(5,2009,'East',12600),(6,2009,'East',13388),
(7,2009,'East',12495),(8,2009,'East',12180),(9,2009,'East',12705),
(10,2009,'East',13650),(11,2009,'East',15750),(12,2009,'East',16800),
(1,2009,'South',11550),(2,2009,'South',10973),(3,2009,'South',12705),
(4,2009,'South',13283),(5,2009,'South',13860),(6,2009,'South',14726),
(7,2009,'South',13745),(8,2009,'South',13398),(9,2009,'South',13976),
(10,2009,'South',15015),(11,2009,'South',17325),(12,2009,'South',18480),
(1,2009,'North',8925),(2,2009,'North',8479),(3,2009,'North',9818),
(4,2009,'North',10264),(5,2009,'North',10710),(6,2009,'North',11379),
(7,2009,'North',10621),(8,2009,'North',10353),(9,2009,'North',10799),
(10,2009,'North',11603),(11,2009,'North',13388),(12,2009,'North',14280),
(1,2009,'West',13650),(2,2009,'West',12968),(3,2009,'West',15015),
(4,2009,'West',15698),(5,2009,'West',16380),(6,2009,'West',17404),
(7,2009,'West',16244),(8,2009,'West',15834),(9,2009,'West',16517),
(10,2009,'West',17745),(11,2009,'West',20475),(12,2009,'West',21840),
(1,2010,'East',11025),(2,2010,'East',10474),(3,2010,'East',12128),
(4,2010,'East',12679),(5,2010,'East',13230),(6,2010,'East',14057),
(7,2010,'East',13120),(8,2010,'East',12789),(9,2010,'East',13340),
(10,2010,'East',14333),(11,2010,'East',16538),(12,2010,'East',17640),
(1,2010,'South',12128),(2,2010,'South',11521),(3,2010,'South',13340),
(4,2010,'South',13947),(5,2010,'South',14553),(6,2010,'South',15463),
(7,2010,'South',14432),(8,2010,'South',14068),(9,2010,'South',14674),
(10,2010,'South',15766),(11,2010,'South',18191),(12,2010,'South',19404),
(1,2010,'North',9371),(2,2010,'North',8903),(3,2010,'North',10308),
(4,2010,'North',10777),(5,2010,'North',11246),(6,2010,'North',11948),
(7,2010,'North',11152),(8,2010,'North',10871),(9,2010,'North',11339),
(10,2010,'North',12183),(11,2010,'North',14057),(12,2010,'North',14994),
(1,2010,'West',14333),(2,2010,'West',13616),(3,2010,'West',15766),
(4,2010,'West',16482),(5,2010,'West',17199),(6,2010,'West',18274),
(7,2010,'West',17056),(8,2010,'West',16626),(9,2010,'West',17342),
(10,2010,'West',18632),(11,2010,'West',21499),(12,2010,'West',22932)
)n(mth,yr,region,sales)
GROUP by yr
ORDER BY 1

This produces the following result.
yr                 AVEDEV
----------- ----------------------
2008       2185.50694444445
2009       2294.77951388889
2010             2409.53125

(3 row(s) affected)

In this example, we calculate the average deviation by region.
SELECT region,
wct.AVEDEV(sales) AVEDEV
FROM (VALUES
(1,2008,'East',10000),(2,2008,'East',9500),(3,2008,'East',11000),
(4,2008,'East',11500),(5,2008,'East',12000),(6,2008,'East',12750),
(7,2008,'East',11900),(8,2008,'East',11600),(9,2008,'East',12100),
(10,2008,'East',13000),(11,2008,'East',15000),(12,2008,'East',16000),
(1,2008,'South',11000),(2,2008,'South',10450),(3,2008,'South',12100),
(4,2008,'South',12650),(5,2008,'South',13200),(6,2008,'South',14025),
(7,2008,'South',13090),(8,2008,'South',12760),(9,2008,'South',13310),
(10,2008,'South',14300),(11,2008,'South',16500),(12,2008,'South',17600),
(1,2008,'North',8500),(2,2008,'North',8075),(3,2008,'North',9350),
(4,2008,'North',9775),(5,2008,'North',10200),(6,2008,'North',10838),
(7,2008,'North',10115),(8,2008,'North',9860),(9,2008,'North',10285),
(10,2008,'North',11050),(11,2008,'North',12750),(12,2008,'North',13600),
(1,2008,'West',13000),(2,2008,'West',12350),(3,2008,'West',14300),
(4,2008,'West',14950),(5,2008,'West',15600),(6,2008,'West',16575),
(7,2008,'West',15470),(8,2008,'West',15080),(9,2008,'West',15730),
(10,2008,'West',16900),(11,2008,'West',19500),(12,2008,'West',20800),
(1,2009,'East',10500),(2,2009,'East',9975),(3,2009,'East',11550),
(4,2009,'East',12075),(5,2009,'East',12600),(6,2009,'East',13388),
(7,2009,'East',12495),(8,2009,'East',12180),(9,2009,'East',12705),
(10,2009,'East',13650),(11,2009,'East',15750),(12,2009,'East',16800),
(1,2009,'South',11550),(2,2009,'South',10973),(3,2009,'South',12705),
(4,2009,'South',13283),(5,2009,'South',13860),(6,2009,'South',14726),
(7,2009,'South',13745),(8,2009,'South',13398),(9,2009,'South',13976),
(10,2009,'South',15015),(11,2009,'South',17325),(12,2009,'South',18480),
(1,2009,'North',8925),(2,2009,'North',8479),(3,2009,'North',9818),
(4,2009,'North',10264),(5,2009,'North',10710),(6,2009,'North',11379),
(7,2009,'North',10621),(8,2009,'North',10353),(9,2009,'North',10799),
(10,2009,'North',11603),(11,2009,'North',13388),(12,2009,'North',14280),
(1,2009,'West',13650),(2,2009,'West',12968),(3,2009,'West',15015),
(4,2009,'West',15698),(5,2009,'West',16380),(6,2009,'West',17404),
(7,2009,'West',16244),(8,2009,'West',15834),(9,2009,'West',16517),
(10,2009,'West',17745),(11,2009,'West',20475),(12,2009,'West',21840),
(1,2010,'East',11025),(2,2010,'East',10474),(3,2010,'East',12128),
(4,2010,'East',12679),(5,2010,'East',13230),(6,2010,'East',14057),
(7,2010,'East',13120),(8,2010,'East',12789),(9,2010,'East',13340),
(10,2010,'East',14333),(11,2010,'East',16538),(12,2010,'East',17640),
(1,2010,'South',12128),(2,2010,'South',11521),(3,2010,'South',13340),
(4,2010,'South',13947),(5,2010,'South',14553),(6,2010,'South',15463),
(7,2010,'South',14432),(8,2010,'South',14068),(9,2010,'South',14674),
(10,2010,'South',15766),(11,2010,'South',18191),(12,2010,'South',19404),
(1,2010,'North',9371),(2,2010,'North',8903),(3,2010,'North',10308),
(4,2010,'North',10777),(5,2010,'North',11246),(6,2010,'North',11948),
(7,2010,'North',11152),(8,2010,'North',10871),(9,2010,'North',11339),
(10,2010,'North',12183),(11,2010,'North',14057),(12,2010,'North',14994),
(1,2010,'West',14333),(2,2010,'West',13616),(3,2010,'West',15766),
(4,2010,'West',16482),(5,2010,'West',17199),(6,2010,'West',18274),
(7,2010,'West',17056),(8,2010,'West',16626),(9,2010,'West',17342),
(10,2010,'West',18632),(11,2010,'West',21499),(12,2010,'West',22932)
)n(mth,yr,region,sales)
GROUP by region
ORDER BY 1

This produces the following result.
region                 AVEDEV
------ ----------------------
East         1467.99691358025
North        1247.77777777778
South        1614.70987654321
West          1908.3024691358

(4 row(s) affected)

In this example calculate the average deviation by year and region.
SELECT region,
yr,
wct.AVEDEV(sales) AVEDEV
FROM (VALUES
(1,2008,'East',10000),(2,2008,'East',9500),(3,2008,'East',11000),
(4,2008,'East',11500),(5,2008,'East',12000),(6,2008,'East',12750),
(7,2008,'East',11900),(8,2008,'East',11600),(9,2008,'East',12100),
(10,2008,'East',13000),(11,2008,'East',15000),(12,2008,'East',16000),
(1,2008,'South',11000),(2,2008,'South',10450),(3,2008,'South',12100),
(4,2008,'South',12650),(5,2008,'South',13200),(6,2008,'South',14025),
(7,2008,'South',13090),(8,2008,'South',12760),(9,2008,'South',13310),
(10,2008,'South',14300),(11,2008,'South',16500),(12,2008,'South',17600),
(1,2008,'North',8500),(2,2008,'North',8075),(3,2008,'North',9350),
(4,2008,'North',9775),(5,2008,'North',10200),(6,2008,'North',10838),
(7,2008,'North',10115),(8,2008,'North',9860),(9,2008,'North',10285),
(10,2008,'North',11050),(11,2008,'North',12750),(12,2008,'North',13600),
(1,2008,'West',13000),(2,2008,'West',12350),(3,2008,'West',14300),
(4,2008,'West',14950),(5,2008,'West',15600),(6,2008,'West',16575),
(7,2008,'West',15470),(8,2008,'West',15080),(9,2008,'West',15730),
(10,2008,'West',16900),(11,2008,'West',19500),(12,2008,'West',20800),
(1,2009,'East',10500),(2,2009,'East',9975),(3,2009,'East',11550),
(4,2009,'East',12075),(5,2009,'East',12600),(6,2009,'East',13388),
(7,2009,'East',12495),(8,2009,'East',12180),(9,2009,'East',12705),
(10,2009,'East',13650),(11,2009,'East',15750),(12,2009,'East',16800),
(1,2009,'South',11550),(2,2009,'South',10973),(3,2009,'South',12705),
(4,2009,'South',13283),(5,2009,'South',13860),(6,2009,'South',14726),
(7,2009,'South',13745),(8,2009,'South',13398),(9,2009,'South',13976),
(10,2009,'South',15015),(11,2009,'South',17325),(12,2009,'South',18480),
(1,2009,'North',8925),(2,2009,'North',8479),(3,2009,'North',9818),
(4,2009,'North',10264),(5,2009,'North',10710),(6,2009,'North',11379),
(7,2009,'North',10621),(8,2009,'North',10353),(9,2009,'North',10799),
(10,2009,'North',11603),(11,2009,'North',13388),(12,2009,'North',14280),
(1,2009,'West',13650),(2,2009,'West',12968),(3,2009,'West',15015),
(4,2009,'West',15698),(5,2009,'West',16380),(6,2009,'West',17404),
(7,2009,'West',16244),(8,2009,'West',15834),(9,2009,'West',16517),
(10,2009,'West',17745),(11,2009,'West',20475),(12,2009,'West',21840),
(1,2010,'East',11025),(2,2010,'East',10474),(3,2010,'East',12128),
(4,2010,'East',12679),(5,2010,'East',13230),(6,2010,'East',14057),
(7,2010,'East',13120),(8,2010,'East',12789),(9,2010,'East',13340),
(10,2010,'East',14333),(11,2010,'East',16538),(12,2010,'East',17640),
(1,2010,'South',12128),(2,2010,'South',11521),(3,2010,'South',13340),
(4,2010,'South',13947),(5,2010,'South',14553),(6,2010,'South',15463),
(7,2010,'South',14432),(8,2010,'South',14068),(9,2010,'South',14674),
(10,2010,'South',15766),(11,2010,'South',18191),(12,2010,'South',19404),
(1,2010,'North',9371),(2,2010,'North',8903),(3,2010,'North',10308),
(4,2010,'North',10777),(5,2010,'North',11246),(6,2010,'North',11948),
(7,2010,'North',11152),(8,2010,'North',10871),(9,2010,'North',11339),
(10,2010,'North',12183),(11,2010,'North',14057),(12,2010,'North',14994),
(1,2010,'West',14333),(2,2010,'West',13616),(3,2010,'West',15766),
(4,2010,'West',16482),(5,2010,'West',17199),(6,2010,'West',18274),
(7,2010,'West',17056),(8,2010,'West',16626),(9,2010,'West',17342),
(10,2010,'West',18632),(11,2010,'West',21499),(12,2010,'West',22932)
)n(mth,yr,region,sales)
GROUP by region, yr
ORDER BY 1, 2

This produces the following result.
region          yr                 AVEDEV
------ ----------- ----------------------
East          2008       1327.77777777778
East          2009       1394.22222222222
East          2010       1463.94444444444
North         2008       1128.66666666667
North         2009       1185.05555555556
North         2010       1244.27777777778
South         2008       1460.55555555556
South         2009       1533.44444444444
South         2010       1610.27777777778
West          2008       1726.11111111111
West          2009       1812.33333333333

West          2010                   1903