DEgorov DEgorov - 4 years ago 118
SQL Question

Data compaction in (My)SQL

I've got a table with 2 columns. The first one is the auto-increment one and the second holds some numeric value. I need to group rows in such a way: get N consecutive rows (using the auto-increment field), count average of corresponding numeric values and put this 1 new line to the other table. Can anyone help me with the GROUP BY statement?

Answer Source

no need for group by ?

SELECT floor(id / 5) cnt,avg(2ndcol) from <table> group by cnt;

This will get groups of 5 rows and return the average of 2ndcol. Example http://www.sqlize.com/y4mTuDF1Cy

avg() docs

edited as per comments

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download