Join Question

Hi all,

I have 2 tables:

CUSTOMERS (business_name, customer_name, renewal_date, prospect_source)
SALES (business_name, amount, date)

prospect_source is used to store the name of the person or advertising resource which generated the business - kind of like a referrer.

I want to display a table using PHP which shows the "Top Referrers" i.e. who generated the most business. And I want users to be able to sort that table either by number of customers which that referrer has referred, or amount of sales that referrer has generated.

I wrote the following query:


SELECT customers.prospect_source, COUNT(*) AS customers_per_prospect,
SUM(sales.amount) AS revenue_per_prospect
FROM customers
INNER JOIN sales
ON customers.business_name=sales.business_name
GROUP BY customers.prospect_source
ORDER BY customers_per_prospect DESC


Which generated the following result:


prospect_source customers_per_prospect revenue_per_prospect
John Smith 5 472.19
Internet Marketer 4 314.95
Google 2 171.13


The problem is, currently "customers_per_prospect" is returning the number of sales that prospect_source has made, and not the number of customers. Because, for any customer, there may be more than 1 sale.

Can somebody help me refine the query to return the number of customers per prospect_source instead?

Thanks in advance
Sean

out of head, if numbers returned are to big

COUNT(distinct(*))


and instead of * use some smart field.

It works!

I used COUNT(DISTINCT(customers.business_name)) in the end.

Thanks
Sean