CODEWITHSUNDEEP
mysqlsql
philwinkle
philwinkle

Reputation: 7056

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(order_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.

Upvotes: 3

Views: 974

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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.

Upvotes: 2

Orbling
Orbling

Reputation: 20602

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).

Upvotes: 3

Related Questions