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 2 of 2

Thread: Difficult Join

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

    Difficult Join

    Hi all,

    I need help building a query please. I have 3 tables:

    OPPORTUNITIES (opportunity_id, opportunity_name, business_name, amount, status, estimated_close...)

    PAYMENTS (opportunity_id, business_name, amount, payment_type, date...)

    CUSTOMERS (business_name, contact_name, prospect_source, telephone, address...)

    An opportunity is a sales term for a possible sale of services to a client. Each payment belongs to an opportunity (sometimes customers make payments in small quantities, so we can have multiple payments belonging to the same opportunity). prospect_source is the name of the individual who referred that customer for business. Kind of like a referrer.

    What I would like is to get a list of top referrers - people who have introduced the most business to the company.

    I already have a way of getting the top referrer by number of companies introduced:

    Code:
    SELECT prospect_source,
    	COUNT( DISTINCT(business_name) ) AS customers_per_prospect
    FROM customers
    GROUP BY prospect_source
    ORDER BY customers_per_prospect DESC;
    And I already have a way of getting the sum of all payments made against an opportunity:

    Code:
    SELECT opportunities.business_name,
           SUM(payments.amount) AS revenue_per_opportunity
    FROM opportunities
    INNER JOIN payments
    ON opportunities.opportunity_id=payments.opportunity_id
    GROUP BY opportunities.business_name
    ORDER BY revenue_per_opportunity DESC;
    What I would like is a way of getting the top referrer by amount of revenue brought in.

    So we need to somehow use information from all 3 tables.

    Does anyone have any idea of how to do this?

    *EDIT* I forgot to mention that I'd like to present these results in a table called "Top Referrers", with the ability for the user to sort the results either by "Number Of Customers" or by "Revenue Brought". My point is that I need a query that will somehow incorporate the first snippet of code I posted above.

    Thanks in advance
    Sean
    Last edited by seanhalley; 11-30-2011 at 11:29 AM.

  • #2
    New Coder
    Join Date
    Jul 2009
    Location
    London, UK
    Posts
    58
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I figured it out:

    Code:
    SELECT 		c.prospect_source,
    								COUNT( DISTINCT(c.business_name) ) AS customers_per_prospect,
    								SUM(p.amount) AS revenue_per_prospect
    					FROM 		opportunities o
    					LEFT OUTER JOIN	customers c ON o.business_name =  c.business_name
    					RIGHT OUTER JOIN payments  p ON o.opportunity_id = p.opportunity_id
    					GROUP BY 	prospect_source
    					ORDER BY 	revenue_per_prospect DESC


  •  

    Posting Permissions

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