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!
精彩评论