Need help with subquery using COUNT aggregation

[dsurls]

I have two tables in Access that look like this:

Sales
Product Salesman Date Revenue
Knife Joe 8/1/2007 1
Knife Joe 8/2/2007 10
Knife Joe 8/3/2007 15
Fork Mike 8/1/2007 5
Fork Mike 8/3/2007 15
Fork Bill 8/1/2007 5
Fork Bill 8/3/2007 20
Spoon Mike 8/1/2007 5
Spoon Mike 8/2/2007 10
Spoon Bill 8/1/2007 20
Spoon Bill 8/3/2007 25

ProductOrder
Product Order
Fork 1
Knife 2
Spoon 3

This query produces the following recordset:

SELECT Sales.Product, Sum(Sales.Revenue) AS TotalRevenue
FROM Sales LEFT JOIN ProductOrder ON Sales.Product = ProductOrder.Product
GROUP BY Sales.Product, ProductOrder.Order
ORDER BY ProductOrder.Order;

Product TotalRevenue
Fork 45
Knife 26
Spoon 60

How can I modify the query to get this recordset (in a single query)?

Product TotalRevenue NumSalesman AvgPerSalesman
Fork 45 2 45
Knife 26 1 13
Spoon 60 2 30

Thanks for the help!!

David

 

[lespaul]

maybe:

SELECT Sales.Product, Sum(Sales.Revenue) AS TotalRevenue, Count(Sales.Salesman) As NumSalesMan, (Sum(Sales.Revenue)/Count(Sales.Salesman)) As AvgPerSalesman
FROM Sales LEFT JOIN ProductOrder ON Sales.Product = ProductOrder.Product
GROUP BY Sales.Product, ProductOrder.Order
ORDER BY ProductOrder.Order;

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dsurls]

Thanks, Leslie, but unfortunately that will not work because it does not count the unique number of Salesman for each Product. In other words, it returns:

Product TotalRevenue NumSalesman AvgPerSalesman
Fork 45 4 11.25
Knife 26 3 8.666667
Spoon 60 4 15

 

[lespaul]

ok what about:

SELECT Sales.Product, Sum(Sales.Revenue) AS TotalRevenue, Count(B.Salesman) As NumSalesMan, (Sum(Sales.Revenue)/Count(B.Salesman)) As AvgPerSalesman
FROM Sales 
INNER JOIN (SELECT Distinct Product, Salesman FROM SALES) As B ON Sales.Product = B.Product
LEFT JOIN ProductOrder ON Sales.Product = ProductOrder.Product
GROUP BY Sales.Product, ProductOrder.Order
ORDER BY ProductOrder.Order;

 

[dsurls]

Thanks, Leslie, but there was a syntax error in your SQL. However, I did figure it out with the help of a colleague (see below). Thanks for the help!

SELECT Product, Sum(SalesmanRevenue) AS TotalRevenue, Count(Salesman) AS NumSaleman, Sum(SalesmanRevenue)/Count(Salesman) AS AvgPerSalesman FROM

(SELECT Sales.Product, Sales.Salesman, ProductOrder.Order AS ProdOrder, Sum(Sales.Revenue) AS SalesmanRevenue
FROM Sales LEFT JOIN ProductOrder ON Sales.Product = ProductOrder.Product
GROUP BY Sales.Product, Sales.Salesman, ProductOrder.Order
)

GROUP BY PRODUCT, ProdOrder
ORDER BY ProdOrder;

 

[lespaul]

glad you got it figured out!

leslie