CODEWITHSUNDEEP
sqlms-accessselectsum
KingJohnno
KingJohnno

Reputation: 602

Sum of all values, based on a calculated field

I am trying to select the total amount of commission earned by a single employee based on monthly stock sales. , I have three tables (tbl_carForSale, tbl_Commision and tbl_employee).

I can get the total value of the all of the cars that the employee has sold, but what I want to do is to be able to calculate the total amount of commission that the employee would have earned (it is a variable commission that is based on the price of the product) for example if the product is £1000, then the amount of commission is 1% (to calculate this is is done by taking the price of the product from the product table and then working out if it is between the min and max value and returning the commission).

SELECT tbl_carForSale.name as 'employee Name', SUM(tbl_carForSale.carPrice) AS    
value of cars sold'   
INNER JOIN tbl_carForSale ON tbl_employee.employeeNo = tbl_carForSale.employeeNo
WHERE tbl_carForSale.soldDate BETWEEN  DATEADD("m", -1 ,Date()) AND Date() AND
tbl_carForSale.bolSold = true 
GROUP BY tbl_employee.name

**bolSold - is a true/false field that indicates if the car has been marked as being 'sold' or not.

I know that I would need to use a nested select statement, but really have no idea, about how I would be able to sum up the commission's as well as the car price? I am guessing that I could do it with two statement - but am not to sure at all..

Edit: The Com table:

ComNo   minValue    maxValue    comAvliable
0            0           5000          2
1            5001       10000          3
2            10001      15000          4
3            15001   99999999          5

Upvotes: 1

Views: 2153

Answers (1)

MikeTWebb
MikeTWebb

Reputation: 9279

I think you can join on the Com table and get your values. By joining on the Com table with a conditional join, the desired comAvailable percentage is returned. Then sum on that * carPrice:

SELECT tbl_carForSale.name as 'employee Name', 
    SUM(tbl_carForSale.carPrice) AS    'value of cars sold'   
    SUM(tbl_carForSale.carPrice * tc.comAvailable) AS    'commission on cars sold'   
INNER JOIN tbl_carForSale ON 
    tbl_employee.employeeNo = tbl_carForSale.employeeNo
INNER JOIN tbl_Com tc ON 
    tbl_carForSale.carPrice >= tc.minValue 
    and
    tbl_carForSale.carPrice <= tc.maxValue    
WHERE tbl_carForSale.soldDate BETWEEN  DATEADD("m", -1 ,Date()) AND Date() AND
tbl_carForSale.bolSold = true 
GROUP BY tbl_employee.name

What you'll need to be careful of is the data in Com table. if the INNER JOIN tbl_Com tc returns more than one row for the carPrice/comAvailable mapping, then it will generate an error

Upvotes: 1

Related Questions