too many counts!

[Svear]

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!

[June7]

You want the average amount customers paid for each product? This will probably require subqueries. An example from my data:

SELECT TestNum, Sum(TimeCharge) AS SumOfTimeCharge, (SELECT Count(TestNum) As CountTN FROM (SELECT DISTINCT TestNum FROM Tests) As Qry1; ) As TotalTests, SumOfTimeCharge/TotalTests As Average
FROM Tests
GROUP BY TestNum;

My source data was all in one table. I expect your source data will be a join of the Customer_Sales and Sales. By the sample output, the Customer table is irrelevant to the calculation.

If you need more help with adapting the query, provide data (spreadsheet or Access).