getting records and calculate

[lostchild]

Hi,

I want to create a select statement with whom i can get the name of a person and the total price of all his purchases from the db

i have 3 tables with fields

catalog/prod_id/price/and so on but not important

customer/cust_id/cust_name/and so on but not important

orders/order_id/prod_id/cust_id/qty

i have tried the following statement but it gave 176 as result of total purchase for each customer and that is not correct

SELECT cust_name, (SUM(prod_id) * (SUM(qty)) FROM orders JOIN catalog GROUP BY cust_name

Can anybody give me a direction on the correct statement?

[lig]

You need a WHERE clause to restrict the output… Maybe something like this…

SELECT cust_name, (SUM(prod_id) * (SUM(qty))
FROM orders o, customer c, catalog cat
WHERE o.cust_id = c.cust_id AND o.prod_id = cat.prod_id
GROUP BY cust_name

This isn’t perfect so you will have to tweek it based on your criteria.