WordPress Query Help

Trying to build a somewhat complicated WordPress query. In one query, I'm trying to:

1. Pull all WordPress authors
2. Only authors with 10 or more published posts(what I'm having trouble with)
3. Sort authors by latest post.

Here is my original query:

SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name ORDER BY date DESC;

This query returns all authors even those with 9 or less published posts.

Here is the query with the count for the posts:

SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date, COUNT(post_date) as post_count FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name ORDER BY date DESC;

In this query you can see that I've added:

COUNT(post_date) as post_count

Which returns everything beautifully.

It's only when I add this WHERE clause, does the query break

post_count > 9

I get this error message:

Unknown colum开发者_运维百科n 'post_count' in 'where clause'

Any idea why this is happing? My theories:

1. Won't work with grouping
2. or MySQL doesn't allow more than one AS statement

If you could shed some light, I would greatly appreciate it.

Thanks.

change the condition in the WHERE part of the query post_count > 9

with HAVING post_count > 9 after the GROUP BY

the final query will be SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date, COUNT(post_date) as post_count FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name HAVING post_count > 9 ORDER BY date DESC;

You're looking for the "HAVING" operator.

SELECT wp_users.ID, display_name, user_url, user_email, MAX(post_date) as date, COUNT(post_date) as post_count FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY display_name HAVING post_count>9 ORDER BY date DESC;