# MYSQL Sum from two tables, joins AGGHH



## Jen_Brown (Jan 3, 2003)

The scenario is this 
Table one - Customer list (name, address, phone)with CustomerName as Primary Key
Table Two - Product list (name, cost)with ProductName as Primary Key
Table three - Invoice list with two fields - CustomerName and ProductName
Very simple I know but I'm only learning...... (12 hours!)
Customers may have many invoices but each one has only one item on it.
My problem is I'm trying to get a list of customer names and totals for only one product
i.e. customer number 1 has ordered this product 6 times
customer number 2 has ordered it 4 times etc

I know the statement should be something like 
select sum (product.productname)as total, customer.customername from customer, product, invoice where invoice.productname='certain product' group by customername;

I know that is probably really wrong but what should it be like? I know there should be some joins in there but left, right, inner????

Please help before I strangle that cat in desperation!


----------



## Jen_Brown (Jan 3, 2003)

An update to the above - I have done the statement in Access (it works...sort of) but when I try to write in in MySQL I run out of characters...??????


----------



## deuce868 (Nov 2, 2000)

> I run out of characters


What?

See if this using the group by helps you out. 
http://groups.google.com/groups?q=f...e=UTF-8&[email protected]&rnum=1


----------

