AVEDEV
Updated 2023-10-20 21:12:31.403000
Syntax
SELECT [westclintech].[wct].[AVEDEV] (
<@x, float,>)
Description
Use the aggregate function AVEDEV to calculate the average of the absolute deviations of data points from their mean. The equation for average deviation is
\frac{1}{N}\sum^{N-1}_{j=0}|x_j-\bar{x}|
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 Type
float
Remarks
AVEDEV is an aggregate function and follows the same conventions as all other AGGREGATE functions in SQL Server.
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.
{"columns":[{"field":"AVEDEV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"AVEDEV":"2354.4212962963"}]}
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.
{"columns":[{"field":"yr","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"AVEDEV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"yr":"2008","AVEDEV":"2185.50694444445"},{"yr":"2009","AVEDEV":"2294.77951388889"},{"yr":"2010","AVEDEV":"2409.53125"}]}
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.
{"columns":[{"field":"region"},{"field":"AVEDEV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"region":"East","AVEDEV":"1467.99691358025"},{"region":"North","AVEDEV":"1247.77777777778"},{"region":"South","AVEDEV":"1614.70987654321"},{"region":"West","AVEDEV":"1908.3024691358"}]}
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.
{"columns":[{"field":"region"},{"field":"yr","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"AVEDEV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"region":"East","yr":"2008","AVEDEV":"1327.77777777778"},{"region":"East","yr":"2009","AVEDEV":"1394.22222222222"},{"region":"East","yr":"2010","AVEDEV":"1463.94444444444"},{"region":"North","yr":"2008","AVEDEV":"1128.66666666667"},{"region":"North","yr":"2009","AVEDEV":"1185.05555555556"},{"region":"North","yr":"2010","AVEDEV":"1244.27777777778"},{"region":"South","yr":"2008","AVEDEV":"1460.55555555556"},{"region":"South","yr":"2009","AVEDEV":"1533.44444444444"},{"region":"South","yr":"2010","AVEDEV":"1610.27777777778"},{"region":"West","yr":"2008","AVEDEV":"1726.11111111111"},{"region":"West","yr":"2009","AVEDEV":"1812.33333333333"},{"region":"West","yr":"2010","AVEDEV":"1903"}]}