I also could just create to separate selects grouped by clientid for both SUM(forsale_log.id) and SUM(forsale.price). It's a bit gross but I am only expecting a hand full of rows returned on each select.
Thanks,
Dan
On Monday, March 3, 2003, at 08:11 AM, Bruce Feist wrote:
Dan Tappin wrote:
I am sure I just have the wrong JOIN structure but I can't figure it out. Any help would be appreciated.
Ah, if only it were that easy!
The problem is that you're really trying to summarize at two ways at the same time, and SQL doesn't like to do that. On one hand you're aggregating sales in client; on the other you're aggregating rows in forsale_log. If you left out the GROUP BY (and adjusted the rest of the query accordingly, eliminating the count() and sum()), you'd see that you're getting one row for each *log hit*, instead of one row for each sale, before summarization. (Actually, you're also getting a row for eachsale without any log hits, since it's a left join, but that's irrelevant to my comments, although correct.) So, when you do the aggregation, each sale is counted multiple times.
In a full implementation of SQL, you'd use a view to get around this. In MySQL, you don't have that luxury, so the best you can do is create a temporary table and use it. It would be something like this:
create table client as select clientID, sum(price) as totPrice, count(*) as saleCount from forsale group by clientID;
select f.clientID, f.totPrice, f.saleCount, count(*) as hits from forsale f LEFT JOIN forsale_log l ON f.clientID = l.clientID group by clientID, totPrice, saleCount;
drop table client;
I'm new to MySQL, and I haven't tested the above, so there could be minor errors. One minor bit of weirdness in the above that I'd better explain is the grouping by totPrice and saleCount. That's there because in an aggregate query it's only possible to select items grouped by or aggregates. *We* know that there will be only one value of totPrice and one of saleCount for each clientID, but SQL doesn't, unless we do the grouping that way. An alternative would be to select max(f.totPrice) and max(f.saleCount) instead.
Bruce Feist
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
