Re: Crazy SELECT SUM ... JOIN ... GROUP problem

2003-03-03 Thread Dan Tappin 
To add to this I am using this data via PHP.  I think it might be 
faster and easier to just create my summary via one select for the hits 
and then an inline select for the hits as I loop thru my PHP code to 
display the list of clients.

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

Re: Crazy SELECT SUM ... JOIN ... GROUP problem

2003-03-03 Thread Bruce Feist 
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

Crazy SELECT SUM ... JOIN ... GROUP problem

2003-03-03 Thread Dan Tappin 
I have a complex JOIN statement that I can't seem to get to work.

Here is my schema...

I have a 'forsale' table with various colunms. Here is the query for  
the data I am interested in:

SELECT
id,
clientid,
price
FROM
forsale
WHERE user_id = 152
+--+-++
|id|clientid |price   |
+--+-++
| 2863 | 317 |  27500 |
| 2864 | 317 |  5 |
| 2865 | 317 |  24000 |
| 2866 | 317 |   2315 |
| 2867 | 317 |   7968 |
| 2868 | 317 |  0 |
| 2869 | 317 |  0 |
| 2872 |  51 |  0 |
+--+-++
8 rows selected.

Each 'forsale' row has an associated clientid which I want to GROUP BY  
to make a summary like this:

SELECT
clientid,
SUM(price)
FROM
forsale
WHERE user_id = 152
GROUP BY clientid
+-+---+
|clientid |SUM(price) |
+-+---+
|  51 |   |
| 317 | 111783|
+-+---+
2 rows selected.

Now I also have a 'forsale_log' table that tracks the number of hits a  
particular 'forsale' row gets on the web side of things:

SELECT
forsale.id,
forsale.clientid,
forsale.price,
COUNT(forsale_log.id) as hits
FROM
forsale
LEFT JOIN forsale_log
ON forsale_log.forsaleid = forsale.id
WHERE user_id = 152
GROUP BY forsale.id
+--+-+ 
+---+
|id|clientid |price   |hits  
  |
+--+-+ 
+---+
| 2863 | 317 |  27500 |  
2 |
| 2864 | 317 |  5 |  
1 |
| 2865 | 317 |  24000 |  
1 |
| 2866 | 317 |   2315 |  
2 |
| 2867 | 317 |   7968 |  
2 |
| 2868 | 317 |  0 |  
4 |
| 2869 | 317 |  0 |  
5 |
| 2872 |  51 |  0 |  
0 |
+--+-+ 
+---+

8 rows selected.

Now if I go and try to GROUP BY the clientid again to get a summary of  
both the hits AND total price I get crazy results:

SELECT
forsale.clientid,
SUM(forsale.price),
COUNT(forsale_log.id) as hits
FROM
forsale
LEFT JOIN forsale_log
ON forsale_log.forsaleid = forsale.id
WHERE user_id = 152
GROUP BY forsale.clientid
+-++---+
|clientid |SUM(forsale.price)  |hits   |
+-++---+
|  51 || 0 |
| 317 | 149566 |17 |
+-++---+
2 rows selected.

The hits are correct but the price total is wrong and it is out by  
factors of the individual 'forsale' items individual 'hits'.

I am sure I just have the wrong JOIN structure but I can't figure it  
out.  Any help would be appreciated.

Thanks,

Dan

-
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

Crazy SELECT SUM ... JOIN ... GROUP problem

2003-03-01 Thread Dan Tappin 
Oops... forgot the subject line...

I have a complex JOIN statement that I can't seem to get to work.

Here is my schema...

I have a 'forsale' table with various colunms. Here is the query for  
the data I am interested in:

SELECT
id,
clientid,
price
FROM
forsale
WHERE user_id = 152
+--+-++
|id|clientid |price   |
+--+-++
| 2863 | 317 |  27500 |
| 2864 | 317 |  5 |
| 2865 | 317 |  24000 |
| 2866 | 317 |   2315 |
| 2867 | 317 |   7968 |
| 2868 | 317 |  0 |
| 2869 | 317 |  0 |
| 2872 |  51 |  0 |
+--+-++
8 rows selected.

Each 'forsale' row has an associated clientid which I want to GROUP BY  
to make a summary like this:

SELECT
clientid,
SUM(price)
FROM
forsale
WHERE user_id = 152
GROUP BY clientid
+-+---+
|clientid |SUM(price) |
+-+---+
|  51 |   |
| 317 | 111783|
+-+---+
2 rows selected.

Now I also have a 'forsale_log' table that tracks the number of hits a  
particular 'forsale' row gets on the web side of things:

SELECT
forsale.id,
forsale.clientid,
forsale.price,
COUNT(forsale_log.id) as hits
FROM
forsale
LEFT JOIN forsale_log
ON forsale_log.forsaleid = forsale.id
WHERE user_id = 152
GROUP BY forsale.id
+--+-+ 
+---+
|id|clientid |price   |hits  
  |
+--+-+ 
+---+
| 2863 | 317 |  27500 |  
2 |
| 2864 | 317 |  5 |  
1 |
| 2865 | 317 |  24000 |  
1 |
| 2866 | 317 |   2315 |  
2 |
| 2867 | 317 |   7968 |  
2 |
| 2868 | 317 |  0 |  
4 |
| 2869 | 317 |  0 |  
5 |
| 2872 |  51 |  0 |  
0 |
+--+-+ 
+---+

8 rows selected.

Now if I go and try to GROUP BY the clientid again to get a summary of  
both the hits AND total price I get crazy results:

SELECT
forsale.clientid,
SUM(forsale.price),
COUNT(forsale_log.id) as hits
FROM
forsale
LEFT JOIN forsale_log
ON forsale_log.forsaleid = forsale.id
WHERE user_id = 152
GROUP BY forsale.clientid
+-++---+
|clientid |SUM(forsale.price)  |hits   |
+-++---+
|  51 || 0 |
| 317 | 149566 |17 |
+-++---+
2 rows selected.

The hits are correct but the price total is wrong and it is out by  
factors of the individual 'forsale' items individual 'hits'.

I am sure I just have the wrong JOIN structure but I can't figure it  
out.  Any help would be appreciated.

Thanks,

Dan

-
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