...

View Full Version : How do I include aggregate info into a detail query ??



mOrloff
07-27-2011, 05:18 PM
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:
Dear Tom, Acme Corps last order was 2/25/11, and ...
Dear Dick, Acme Corps last order was 2/25/11, and ...
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 ...


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 ...


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??

Old Pedant
07-27-2011, 09:24 PM
> 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.


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


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:


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

ABC Inc. 15 July 2011 joe@abc.com

mOrloff
07-27-2011, 09:59 PM
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:


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

Old Pedant
07-27-2011, 10:44 PM
UGH. What a mess.

Okay...



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.

mOrloff
07-27-2011, 10:50 PM
You are fantastic :thumbsup:
Even if you got tired of hearing it, I would say it any way ... you are brilliant.
Thanks again :)

Or ... maybe I should add that into the sub query ... Which would be better??

mOrloff
07-27-2011, 10:58 PM
... 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

Old Pedant
07-27-2011, 11:28 PM
Oh...could do it either way.

Probably slightly more efficient to put it into the sub-query.


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.

mOrloff
07-27-2011, 11:34 PM
so I tried this...

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????

Old Pedant
07-28-2011, 01:29 AM
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???



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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum