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 9 of 9
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts

    How do I include aggregate info into a detail query ??

    I have a sales manager who wants a list of all of our customer contacts for a specific type of customer, and to have the last date of sale to that company regardless of which customer contact we sold to.

    Example: Let's say that Acme Corp has three contacts: Tom, Dick, Harry, and the last sale anyone at Acme Corp was 02/25/2011. He wants to send an email to each of them:
    1. Dear Tom, Acme Corps last order was 2/25/11, and ...
    2. Dear Dick, Acme Corps last order was 2/25/11, and ...
    3. Dear Harry, Acme Corps last order was 2/25/11, and ...

    I am having difficulty fitting MAX(salesorder.datecreated) into the query without causing errors.

    Here's the basics of what I'm working with (note: this DB uses text fields for matches )
    salesorder
    • companyname
    • datecreated
    • ...

    company
    • companyname
    • type
    • ...

    contact
    • name
    • emailaddress
    • companyname
    • ...

    This ...
    Code:
    SELECT DISTINCT contact.emailaddress, company.companyname
    FROM contact
    LEFT JOIN company ON c.companyname=co.companyname
    WHERE company.type = '1'
    AND contact.emailaddress IS NOT NULL
    ORDER BY company.companyname
    Gets me a list of the contacts, but expanding it like this ...
    Code:
    SELECT DISTINCT c.emailaddress, co.companyname, MAX(so.datecreated)
    FROM contact AS c
    LEFT JOIN company AS co ON co.companyname=c.companyname
    LEFT JOIN salesorder AS so ON so.companyname=c.companyname
    WHERE co.type = '1'
    AND c.emailaddress IS NOT NULL
    ORDER BY co.companyname
    ... throws an error (naturally).

    I don't want to group the results, I just want each one to have the last sales date to their company.
    I get the feeling I'm way off the mark here.
    Help??
    ...because it's dundant already.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    > I don't want to group the results,

    Yes, you do. Without GROUP BY you can't use aggregate functions properly.

    MySQL will often let you get away with omitting GROUP BY (other databases won't...I think this is a weakness in MySQL), but the results are then less than optimal or (in many cases) predictable.

    Having said that...

    Your query is nonsensical.

    Let's create a couple of records as an example.
    Code:
    COMPANY table
        companyName   Type
        ABC Inc.      xxx
    
    CONTACT table
        name  email       companyname
        Joe   joe@abc.com  ABC Inc.
        Bob   bob@abc.com  ABC Inc.
    
    SALESORDER
        companyName   dateCreated
        ABC Inc.      1 July 2011
        ABC Inc.      15 July 2011
    Okay, FROM THAT DATA, *WHAT* do you want to display??

    Getting companyNamd and the latest dateCreated is easy. You'll get back
    Code:
        ABC Inc.     15 July 2011
    BUT WHICH EMAIL ADDRESS will you return??? How will *YOU* decide which one? How will you tell the computer which one to choose???

    *********************

    I strongly suspect that you omitted a vital piece of the puzzle.

    I'm betting that your SALESORDER table *ALSO* contains the name of the CONTACT that made the sale. That is:
    Code:
    SALESORDER
        companyName   dateCreated  saleMadeBy
        ABC Inc.       1 July 2011  Bob
        ABC Inc.      15 July 2011  Joe
    If that's so, *THEN* we can indeed produce a result of
    Code:
        ABC Inc.      15 July 2011  joe@abc.com

  • #3
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Thanks.
    First ... just to lay it all out there ... this DB is a disaster! I'm no seasoned hand, but even I can recognize the room for improvement (ie: there are frequently several naming conventions within any given table, and usually just as many columns for identical info!). I am sooo glad we will be moving into a better solution at the turn of the year.

    Anyhow, while there is a "buyer" name in each salesorder, a good portion of those contact records are likely obsolete, and I'm being asked to provide a clean dataset from these murky depths.

    So, using your first example tables, the dataset we would want returned is:
    Code:
        Joe    ABC, Inc.    15 July 2011    joe@abc.com
        Bob    ABC, Inc.    15 July 2011    bob@abc.com
    He (the sales manager) is asking for a spreadsheet laid out like that which he can toy with (and re-sort in any way he wants) to his hearts content.

    If there are any other questions I can answer to clarify the mess a little more, I'd be happy to hear them.
    ~ Mo
    ...because it's dundant already.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    UGH. What a mess.

    Okay...

    Code:
    SELECT CT.name, CT.companyName, M.maxdate, CT.email
    FROM contact AS CT,
         ( SELECT companyName, MAX(dateCreated) AS maxdate
           FROM salesorder
           GROUP BY companyName ) AS M
    WHERE CT.companyName = M.companyName
    ORDER BY ...you decide...
    Don't see a need to involve the COMPANY table at all, since you have companyName in all the tables.

  • Users who have thanked Old Pedant for this post:

    mOrloff (07-27-2011)

  • #5
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    You are fantastic
    Even if you got tired of hearing it, I would say it any way ... you are brilliant.
    Thanks again

    Edit: Or ... maybe I should add that into the sub query ... Which would be better??
    Last edited by mOrloff; 07-27-2011 at 10:04 PM.
    ...because it's dundant already.

  • #6
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Old Pedant View Post
    ... Don't see a need to involve the COMPANY table at all, since you have companyName in all the tables.
    Except for Company.type must be 1.

    Should I add do a JOIN on the outer level, or slide it into the subquery?
    ~ Mo
    Last edited by mOrloff; 07-27-2011 at 10:33 PM.
    ...because it's dundant already.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Oh...could do it either way.

    Probably slightly more efficient to put it into the sub-query.
    Code:
    SELECT CT.name, CT.companyName, M.maxdate, CT.email
    FROM contact AS CT,
         ( SELECT CO.companyName, MAX(S.dateCreated) AS maxdate
           FROM company AS CO, salesorder AS S
           WHERE CO.companyName = S.companyName
           AND CO.type = 1
           GROUP BY companyName ) AS M
    WHERE CT.companyName = M.companyName
    ORDER BY ...you decide...
    Putting it in the subquery means fewer records returned from the subquery and so fewer records to join to contact table. Almost surely a savings over joining at the outer level, though might be tiny savings.

  • #8
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    so I tried this...
    Code:
    SELECT CT.name, CT.companyName, M.maxdate, CT.email
    FROM contact AS CT,
         ( SELECT SO.companyName, MAX(SO.dateCreated) AS maxdate
           FROM salesorder AS SO, company AS CO
           WHERE CO.companyname = SO.companyname
           AND CO.type = 1
           GROUP BY SO.companyName ) AS M
    WHERE CT.companyName = M.companyName
    ORDER BY m.maxdate
    ...and it works, except my maxdate value gets returned as (what appears to be) a float (ie: 40749.38).
    It does the same if I drop the company out of the sub-query and run it exactly as you sent it the first time around.

    However, if I run the sub-query by itself, it returns a good date and time.

    Thoughts????
    ...because it's dundant already.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Didn't happen in my little test.

    What's the data type of your dateCreated field? Is it DATETIME or is it TIMESTAMP? I'm going to guess it's probably TIMESTAMP??

    Hmmm...except MAX of a TIMESTAMP field *should* be an integer, not a floating point number.

    Well, I guess you could always coerce it back to the right type???

    Code:
    SELECT CT.name, CT.companyName, CAST( M.maxdate AS DATETIME ) AS maxDateCreated, CT.email
    FROM contact AS CT,
         ( SELECT SO.companyName, MAX(SO.dateCreated) AS maxdate
           FROM salesorder AS SO, company AS CO
           WHERE CO.companyname = SO.companyname
           AND CO.type = 1
           GROUP BY SO.companyName ) AS M
    WHERE CT.companyName = M.companyName
    ORDER BY maxDateCreated
    What version of MySQL are you using? I'm using 5.5.


  •  

    Posting Permissions

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