List:General Discussion« Previous MessageNext Message »
From:Peter Janett Date:April 14 2004 6:45am
Subject:Average of every X number of rows?
View as plain text  
I searched all over and can't seem to find out how to take an average of
every x number of rows from a result set.

I have a table with over 700,000 rows, and I want to display an x and y
graph of that data on the web.  What is happening now is I'm creating 2
arrays, one for the x axis and one for the y axis of the graph, each with ~
700,000 elements.  All that data is used to plot a 600 pixel wide graph, so
in the process of creating the web images, most of the detail is lost, and
it takes ~ 30 seconds to render.  (I'm using jpgraph in PHP, and have
confirmed that the majority of the time needed to render is creating the
image, not populating the large arrays.)

So, I figure rather than plot all that detail, only to have most of it
thrown out when the image is created, I need to throw out the data before
the image is created, in hopes that doing so will greatly decrease the
amount of time needed to render the graph.

So, what I need to do is get the average of a column ever x rows.

This is as far as I got:
SELECT AVG(value) AS every_ten_row_average FROM table GROUP BY [every 10
rows]

The part I'm stuck on is the [every 10 rows].

I think I could use a limit statement, and loop the query over and over,
each time adjusting the limit values by 10, but that would mean a whole lot
of queries. :)

My guess is that I need to create a temp table or a virtual column based on
current row, and use that as my group by.

Any help GREATLY appreciated.

If anyone has solutions for creating web image graphs from MySQL that would
be faster than using the php based jpgraph setup, I'd love to hear about
them as well.

Thank you!

Peter Janett

New Media One Web Services, LLC
http://www.newmediaone.net
(303)828-9882



Thread
Average of every X number of rows?Peter Janett14 Apr
  • Re: Average of every X number of rows?Harald Fuchs16 Apr
Re: Average of every X number of rows?SGreen14 Apr