SUM(DISTINCT) OF INNER JOIN

[Edward]

I have two tables recording the sales transactions with purchase_id the
common key

Table 1 (Recording total amounts and customer info)

Purchase_Id (Primary Key)
Sales_Total (Total sales amount)
Customer_Id
..... (Other fields)

Table 2 (Recording the detail transaction items)
Purchase_Id (Primary Key)
Product_Id (Product information)
...... (Other fields)

Here is a sample record set of my table

Table 1
Purchase_Id Sales_Total Customer_Id
1 200 1
2 300 4
3 500 4

............... ..

Table 2
Purchase_Id Product_Id
1 1001
1 1005
2 1004
3 1001
3 1002
3 1003
............... ......
I need to generate the sales report and caculate the total sales of a
particular customer

Joining these two tables result in
Purhcase_Id Sales_Total Customer_Id Product_Id
1 200 1 1001
1 200 1 1005
2 300 4 1004
3 500 4 1001
3 500 4 1002
3 500 4 1003

I am using Mysql Database, I did select statement like this:
SELECT Customer_Id, SUM(Sales_Total ) FROM Table 1 INNER JOIN Table 2
USING (Purchase_ID) WHERE Product_Id > 1000 GROUP BY Customer_Id

Result In
Customer_Id SUM(Sales_Total )
1 400
4 1800

Which is not correct, I want to count on UNIQUE (DISTINCT) Purchsae_Id
and no need to duplicately sum up on rows.

I.e, the result should be
Customer_Id SUM
1 200
4 800

What should I do???
Thanks.

[Bill Karwin]

"Edward" <ed**********@g mail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

I need to generate the sales report and caculate the total sales of a
particular customer

See my reply to your crosspost on comp.databases. mysql.

Bill K.