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.
Click here for the SQL2005 version of the AVEDEV function
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