SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast AGGrrSSIVE's Avatar
    Join Date
    Mar 2001
    Location
    Hilton Head Island, SC
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Filter Calculations from JOINed MySQL Query

    Objective: I have a sales and commission reporting system where the commission for a particular sale may be split among multiple agents. My report displays a row for each commission record (tCommission AS c) and the corresponding sale (from tBookings AS b) and calculates the totals to display at the bottom of the report.

    Here's the problem: I need to filter the b.numTravelers from calculating for the end report if there is more than one commission associated with the sale so we don't have an inflated total in the final report. I'm getting the correct result rows in my data, but need to keep PHP from calculating the number of travelers for the final report.

    Here's my query:
    Code:
    SELECT b.conf,b.numTravelers,
    c.agentID,c.netPrice,c.agentComm 
    FROM tBookings AS b 
    INNER JOIN tCommission AS c 
    ON c.conf = b.conf 
    WHERE b.bookStatus='Confirmed'
    Sample Result:
    Booking #, Agent, Net Sale, Agent Commission, Passengers Booked
    12345, Drew, $1500, $150, 3
    98765, Tim, $2500, $125, 2
    98765, James, $2500, $125, 2
    45612, Carol, $1200, $120, 4
    Totals: $7700, $520, 11
    Values in question marked in red.


    Additional Information:
    • The results will not always be sorted by b.conf. Users can choose any field to sort by (the main reason for using a joined query).
    • We still need to count one of the duplicated bookings.
    • I did try the same query with a LEFT JOIN, but got the same result. Results isn't my problem, properly calculating them is.
    Thanks for any help I can get on this. Searched the forums last night and today and can't find an answer.
    I just may be the lunatic you're looking for. www.socialbaggage.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i don't get it

    the sample result in red looks like it adds up just fine

    what does "I need to filter the b.numTravelers from calculating for the end report" mean?

    you want to completely drop the rows with red in them?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast AGGrrSSIVE's Avatar
    Join Date
    Mar 2001
    Location
    Hilton Head Island, SC
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tim and James shared the commission on one sale. In our final calculation, the Net Price and Number of Passengers for that sale was counted twice.

    (For some reason I think the above sentence reads like a Junior High math question).

    The items in red have the same booking number (they're the same sale), but have more than one commission record associated with them. This is duplicating the Net Sale and Number of Passengers for our calculations.

    Hope that explains it a little better.
    I just may be the lunatic you're looking for. www.socialbaggage.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, i guess then i would have to say that either your query is wrong in that it presents the same single number more than once to the application which then dutifully totals everything, or else your query needs to show the same single number more than once, and your application is wrong in that it doesn't understand what your query is doing
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i think you have a flaw int he way the data is stored, or you're not displaying the whole picture. how is the commission split? 50/50? or is it different for each one?

    also, what version of mysql are you using?

  6. #6
    SitePoint Enthusiast AGGrrSSIVE's Avatar
    Join Date
    Mar 2001
    Location
    Hilton Head Island, SC
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am only showing a small part of a huge application. For client security purposes, I am not allowed to show more than absolutely necessary.

    MySQL 3.23

    The commissions are split up using another application elsewhere in the software. Other reports / functions are dependent on my storing the data this way.

    I don't think more information is required to answer the base question. If I have two records in table1 associated with one record in table2, how can I list the information and have it not count the info in table 2 twice when totalling things up?
    I just may be the lunatic you're looking for. www.socialbaggage.com

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by AGGrrSSIVE
    I don't think more information is required to answer the base question. If I have two records in table1 associated with one record in table2, how can I list the information and have it not count the info in table 2 twice when totalling things up?
    okay, i don't think you can do it and still show details for both

    totals, or details? which do you want?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast AGGrrSSIVE's Avatar
    Join Date
    Mar 2001
    Location
    Hilton Head Island, SC
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, is there a better way to query and retreive the results I want while minimizing hits to the database?

    We previously had the report running a nested query that pulled accurate results, but really clobbered the db server whenever results got into the hundreds. Took forever to load and really slowed down things.

    The client does thousands of sales each year, so the results displayed can get pretty big. Not to mention, they get a huge amount of traffic, so we're trying to everything possible to keep our applications light, but functional.
    I just may be the lunatic you're looking for. www.socialbaggage.com

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    tell us what the nest queries were doing and we'll see if we can duplicate the results in pure-SQL.


Bookmarks

Posting Permissions

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