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

[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 |      50000 |
|                 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 |      50000 |                      
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