CODEWITHSUNDEEP
mysql
Doug Wolfgram
Doug Wolfgram

Reputation: 2126

How do I query for the sum of averages?

I have data that resemble stock data that is being updated every hour. So there are 24 entries every day for each stock. (just using stock as an example). But sometimes, the data may not be updated.

For example, let's assume we have 3 stocks, A, B, C. And assume that we gather data at various intervals during the day for each stock. The data would look something like this...

row   A    B    C
 1    3    4    5
 2    3.5  4.1  5
 3    2.9  3.8  4.3

What I want is to sum up the average value of each stock for this time period or Avg(A) + Avg(B) + Avg(C)

In reality I have hundreds of stocks and hundreds of thousands of rows. I need this to calculate for a single day.

I tried this (stock names are in an array - stocks = array('A','B','C'))

SELECT SUM(AVG(stock_price)) FROM table WHERE date = [mydate] AND stock_name IN () ('".implode("','", $stocks)."') GROUP BY stock_name

but that didn't work. Can someone provide some insight?

Thanks, in advance.

Upvotes: 0

Views: 63

Answers (2)

spencer7593
spencer7593

Reputation: 108500

One way to do it, use an inline view as a rowsource:

SELECT SUM(a.avg_stock_price) AS sum_avg_stock_price
  FROM ( SELECT AVG(t.stock_price) AS avg_stock_price
           FROM table t
          WHERE t.date = [mydate]
            AND t.stock_name IN ('a','b','c')
          GROUP BY t.stock_name
       ) a

You can run just the query from the inline view (aliased as a) to get verify the results it returns. The outer query runs against the set of rows returned by the inline view query. (MySQL refers to the inline view (aliased as a) as a "derived table".

The outer query is effectively like this:

SELECT SUM(a.avg_stock_price) AS sum_avg_stock_price
  FROM a

The "trick" is that "a" isn't a regular table, it's a set of rows returned by a query; but in terms of relational algebra theory, it works the same... it's a set or rows. If a were a regular table, we could write:

SELECT b.col
  FROM (
         SELECT col FROM a
       ) b

We don't want to do that in MySQL when we don't have to, because of the inefficient way that MySQL processes that. MySQL first runs the inner query (the query in the inline view). MySQL creates a temporary MyISAM table, and inserts the rows returned by the query into the temporary MyISAM table. MySQL then runs the outer query, against that temporary table (which MySQL refers to as a "derived table") to return the result. Creating and populating a temporary table that's a copy of a regular table is a lot of overhead, especially with large sets.

What makes this powerful is that inline view query can include JOINs, WHERE clause, aggregates, GROUP BY, whatever. As long as it returns a set of rows (with appropriate column names), we can wrap the query in parens, and reference it in another query like it was a table.

Upvotes: 1

Barmar
Barmar

Reputation: 782488

Calculate the per-stock averages in a sub-query, then sum them in the main query.

SELECT SUM(average_price) AS total_averages
FROM (SELECT AVG(price) AS average_price)
      FROM table
      WHERE <conditions>
      GROUP BY stock_name) AS averages

Upvotes: 2

Related Questions