Hey!
I got a problem:
I have 3 Tables (table Customer, table Sales and table Customer_Sales).
In the customer table I have about 20 customers. Of these 20 customer there are about 12 customer entries in the table Sales (so 12 of 20 customer already have made a sale). There are about 120 transactions in the Table sales.
So I actually want to count the average amount of purchase price for each products all customers (just an example)
SELECT count(Customer.CustomerIDs) as CAmount, avg(Sales.Price), Sales.ProductID
FROM customer, sales, customer_sales
WHERE customer.customer.ID = customer_sales.customerID AND sales.productID = customer_sales.productID
GROUP BY Sales.ProductID;
But as soon as I connect the two tables, I get a count of 120 customers instead of 12! So The average sales price is calculated with an amount on 120 customers, which gives me a wrong result.
Can someone tell me how I can get the right count of of customers?
The result should be like that:
ProductID Camount AvgPrice 5 12 5,4€ 4 12 3,5€ 3 12 12,5€ 2 12 5 € 1 12 3 €
Can somebody help me? Thanks!