If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"join" and "sum" over 3 tables
Hi,
sorry if this question is a tad bit lame, but I ran out of practice re SQL and and can't seem to solve this on my own. Products ---------------------------------------------------- Product_ID_PK | Product_Name | Product_Price ---------------------------------------------------- 1 | Shirt | $50 2 | Pants | $100 3 | Coat | $200 4 | Belt | $20 [...] Salespersons ---------------------------------------------------- Salesperson_ID_PK | Salesperson_Name ---------------------------------------------------- 1 | James Cooper 2 | Jim Brown 3 | Andrea Smith [...] Sales ---------------------------------------------------- Sales_ID_PK | Product_ID | Salesperson_ID | Amount ---------------------------------------------------- 1 1 | 1 | 2 | 100 2 2 | 2 | 2 | 150 3 3 | 1 | 3 | 1000 4 4 | 2 | 3 | 1000 5 5 | 3 | 3 | 1100 6 6 | 4 | 3 | 500 [...] So, I want to query the volume of sales per salesperson. The result should look something like this: Salesperson | Volume ----------------------- James Cooper | $0 Jim Brown | $20000 Andrea Smith | $380000 [...] i.e.: expr: Products.Product_Price*Sales.Amount Thank you for your attention Joe P.S.: it might take me until Monday to give feedback |
#2
|
|||
|
|||
"join" and "sum" over 3 tables
SELECT SalesPersonName, SUM(ProductPrice)
FROM (Sales INNER JOIN Products ON Sales.ProductID=Products.ProductID) INNER JOIN SalesPerson ON Sales.SalesPersonID=SalesPerson.SalesPersonID GROUP BY SalesPersonName Hoping it may help, Vanderghast, Access MVP "Joe Koenig" wrote in message ... Hi, sorry if this question is a tad bit lame, but I ran out of practice re SQL and and can't seem to solve this on my own. Products ---------------------------------------------------- Product_ID_PK | Product_Name | Product_Price ---------------------------------------------------- 1 | Shirt | $50 2 | Pants | $100 3 | Coat | $200 4 | Belt | $20 [...] Salespersons ---------------------------------------------------- Salesperson_ID_PK | Salesperson_Name ---------------------------------------------------- 1 | James Cooper 2 | Jim Brown 3 | Andrea Smith [...] Sales ---------------------------------------------------- Sales_ID_PK | Product_ID | Salesperson_ID | Amount ---------------------------------------------------- 1 1 | 1 | 2 | 100 2 2 | 2 | 2 | 150 3 3 | 1 | 3 | 1000 4 4 | 2 | 3 | 1000 5 5 | 3 | 3 | 1100 6 6 | 4 | 3 | 500 [...] So, I want to query the volume of sales per salesperson. The result should look something like this: Salesperson | Volume ----------------------- James Cooper | $0 Jim Brown | $20000 Andrea Smith | $380000 [...] i.e.: expr: Products.Product_Price*Sales.Amount Thank you for your attention Joe P.S.: it might take me until Monday to give feedback |
Thread Tools | |
Display Modes | |
|
|