Retrieving data from joined MySQL tables using an AVG in the WHERE clause?

I am trying to select data from multiple tables which uses an AVG in the WHERE clause.

SELECT company_metrics.*, companies.company_name, companies.permalink 
FROM company_metrics LEFT JOIN companies 
     ON companies.company_id = company_metrics.comp开发者_JAVA百科any_id
WHERE MONTH(date) = '04' AND YEAR(date) = '2011' 
HAVING (SELECT avg(company_unique_visitors) 
        FROM (SELECT company_metrics.company_unique_visitors 
              FROM company_metrics  
              ORDER BY company_metrics.date DESC LIMIT 3)
        average ) >'2000' 
ORDER BY date DESC

Example Data:

###Company Metrics#### Table
company_id       company_unique_visitors       date
-----------      -----------------------       ----
     604                    2054               2011-04-01
     604                    3444               2011-03-01
     604                    2122               2011-02-01
     604                    2144               2011-01-01
     604                    2001               2010-12-01
     602                    2011               2011-04-01
     602                    11                 2011-03-01
     602                    411                2011-02-01
     602                    611                2011-01-01
     602                    111                2010-12-01

EDIT I would like only the 3 latest numbers from company_unique_visitors AVG'ed /EDIT

So the query would select company_id 604 but it wouldn't select company_id 602 because 602 doesn't have an AVG greater than 2000.

I need help writing the correct query to do as I have described. I can clarify if needed. Thanks for your help!

---

There are several problems with your query as written. I'm not completely clear as to the structure of all the tables, but I believe I understand the gist based on the query you posted. Your first problem with the posted query is that you're not grouping by or using any aggregates in the query where you're using the HAVING clause. You use aggregates in one of the subqueries, but the HAVING where it is right now doesn't make much sense.

I believe you wanted to group by the company_id before you did an aggregate of the averages, so I made that the primary group by on the outer query. You were also using too many nested queries to accomplish what was was a seemingly simple task of only selecting the 3 most recent measurements. I moved that subquery into the primary join so that the data was only selected once and in a logical way.

And, without further ceremony, here's the fixed query:

SELECT limited_metrics.*, companies.company_name, companies.permalink,
       avg(limited_metrics.company_unique_visitors) AS avg_visitors 
FROM 
  (SELECT *
   FROM company_metrics
   ORDER BY company_metrics.date DESC LIMIT 3) AS limited_metrics
  LEFT JOIN companies 
  ON companies.company_id = limited_metrics.company_id
WHERE MONTH(limited_metrics.date) = '04' AND YEAR(limited_metrics.date) = '2011'
GROUP BY companies.company_id
HAVING avg_visitors > 2000

---

Ok based off of Jared Harding's answer and this post: Moving average - MySQL I was able to figure out the query.

SELECT metrics.*,companies.company_name,companies.permalink
FROM (SELECT company_id,AVG(company_unique_visitors) AS met_avg
      FROM company_metrics
     WHERE `date` BETWEEN DATE_SUB(NOW(), INTERVAL 4 MONTH) AND NOW()
  GROUP BY company_id HAVING met_avg>2000) AS metrics
LEFT JOIN companies ON companies.company_id=metrics.company_id

Thanks Jared for all your help!