Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Join Question

  1. #1
    New Coder
    Join Date
    Jul 2009
    Location
    London, UK
    Posts
    58
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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:

    Code:
    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:

    Code:
    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

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    out of head, if numbers returned are to big
    Code:
    COUNT(distinct(*))
    and instead of * use some smart field.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    New Coder
    Join Date
    Jul 2009
    Location
    London, UK
    Posts
    58
    Thanks
    8
    Thanked 0 Times in 0 Posts
    It works!

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

    Thanks
    Sean


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •