NickName:Douglas Vanny Ask DateTime:2014-02-08T13:26:29

Get Average value for each X rows in SQL

Let`s say I have the following table

+----+-------+
| Id | Value |
+----+-------+
|  1 |   2.0 |
|  2 |   8.0 |
|  3 |   3.0 |
|  4 |   9.0 |
|  5 |   1.0 |
|  6 |   4.0 |
|  7 |   2.5 |
|  8 |   6.5 |
+----+-------+

I want to plot these values, but since my real table has thousands of values, I thought about getting and average for each X rows. Is there any way for me to do so for, ie, each 2 or 4 rows, like below:

2
+-----+------+
| 1-2 |  5.0 |
| 3-4 |  6.0 |
| 5-6 |  2.5 |
| 7-8 |  4.5 |
+-----+------+

4
+-----+------+
| 1-4 |  5.5 |
| 5-8 |  3.5 |
+-----+------+

Also, is there any way to make this X value dynamic, based on the total number of rows in my table? Something like, if I have 1000 rows, the average will be calculated based on each 200 rows (1000/5), but if I have 20, calculate it based on each 4 rows (20/5).

I know how to do that programmatically, but is there any way to do so using pure SQL?

EDIT: I need it to work on mysql.

Copyright Notice：Content Author:「Douglas Vanny」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/21642329/get-average-value-for-each-x-rows-in-sql

Answers

ErikE 2014-02-08T05:54:27

Depending on your DBMS, something like this will work:\n\nSELECT\n ChunkStart = Min(Id),\n ChunkEnd = Max(Id),\n Value = Avg(Value)\nFROM\n (\n SELECT\n Chunk = NTILE(5) OVER (ORDER BY Id),\n *\n FROM\n YourTable\n ) AS T\nGROUP BY\n Chunk\nORDER BY \n ChunkStart;\n\n\nThis creates 5 groups or chunks no matter how many rows there are, as you requested.\n\nIf you have no windowing functions you can fake it:\n\nSELECT\n ChunkStart = Min(Id),\n ChunkEnd = Max(Id),\n Value = Avg(Value)\nFROM\n YourTable\nGROUP BY\n (Id - 1) / (((SELECT Count(*) FROM YourTable) + 4) / 5)\n;\n\n\nI made some assumptions here such as Id beginning with 1 and there being no gaps, and that you would want the last group too small instead of too big if things didn't divide evenly. I also assumed integer division would result as in Ms SQL Server.",

Miro 2014-02-08T05:45:41

You can use modulos operator to act on every Nth row of the table. This example would get the average value for every 10th row:\n\nselect avg(Value) from some_table where id % 10 = 0;\n\n\nYou could then do a count of the rows in the table, apply some factor to that, and use that value as a dynamic interval:\n\nselect avg(Value) from some_table where id % (select round(count(*)/1000) from some_table) = 0;\n\n\nYou'll need to figure out the best interval based on the actual number of rows you have in the table of course.\n\nEDIT:\nRereading you post I realize this is getting an average of every Nth row, and not each sequential N rows. I'm not sure if this would suffice, or if you specifically need sequential averages. ",

Concrete Gannet 2014-02-08T05:56:03

Look at the NTILE function (as in quartile, quintile, decile, percentile). You can use it to split your data evenly into a number of buckets - in your case it seems you would like five.\n\nThen you can use AVG to calculate an average for each bucket.\n\nNTILE is in SQL-99 so most DBMSes should have it.",