View Full Version : Join Question

09-27-2011, 09:55 AM
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
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

09-27-2011, 11:28 AM
out of head, if numbers returned are to big


and instead of * use some smart field.

09-27-2011, 11:46 AM
It works!

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