MySQL count() query returns rows instead of total?

What might be wrong with this query:

select count(customer_email) as num_prev
  from _pj_cust_email_by_date
  where order_date < '2011-02'
  and customer_email is not null
  group by customer_email having count(orde开发者_StackOverflow中文版r_date) > 0;

Which returns row results such as:

1
2
3
2
1
5
4

When I'm trying to get a full count of how many customers in total purchased during the specified date range?

_pj_cust_email_by_date is a view that returns only email address and order date in YYYY-MM-DD format. I do not have access to use anything save for this view.

The GROUP BY is causing that.

It causes one result row to be returned per group, in this for each distinct value of customer_email.

If you want the total number of distinct email addresses, then you need to drop the GROUP BY clause and change the COUNT to COUNT(DISTINCT customer_email).

You need to subquery it further

select count(*) CustomerCount
from (
    select count(customer_email) as num_prev
    from _pj_cust_email_by_date
    where order_date < '2011-02'
    and customer_email is not null
    group by customer_email having count(order_date) > 0;
) as innercount

That would normally be the approach, but since you're using having count(order_date) > 0, I think you only need

select count(distinct customer_email) as num_prev
from _pj_cust_email_by_date
where order_date < '2011-02' and customer_email is not null

Because the HAVING clause will never detail with empty order_dates which makes the HAVING clause a dud, actually.