Select double sum if value=true in another table
I'm not going into far details with my table structure, nor my query, which is kinda huge to be honest. I'll explain an extremely simple scenario where my problem applies:
Salesmen sell items. I am producing a total sales for each salesman, for every month. During some particular month, we double the value of their sales. E.g. in Feb 2012, if Pauline has sold $10,000 worth of items, we record the sales as $20,000.
I have a sales table, e.g.:
Salesman_Id Period Sales Pauline 201201 4200 Johnny 201201 2000 Rowan 201201 8100 Pauline 201202 3300 Johnny 201202 2900 Rowan 201202 1100 Pauline 201203 8000 Johnny 201203 4000 Rowan 201203 3100
I have a sales period table, e.g.:
Sales_Period Double_Period 201201 F 201202 T 201203 F
The sales period table indicates that period 2012/02 is a double period - i.e. the value of the items you have sold is doubled. This means that at the end of the day, I should get:
Salesman_Id Total_Sales Pauline 18800 Johnny 11800 Rowan 13400
Rowan, for example, has sales = 8100 + (1100*2) + 3100 = 13400
My question is therefore: how do I produce a report of the total sales of all my salesmen for every month taking the Double_Period into account? This must not be a tough one. I hope you guys can help.
Here you need to use SUM() function to get Total Sales. And you need to use CASE statement to multiply the sales amount in case of Double_Period = T. So try this:
SELECT s.Salesman_Id , SUM(CASE WHEN sp.Double_Period = 'T' THEN 2 * s.Sales ELSE s.Sales END) AS Total_Sales FROM Sales s JOIN Sales_period sp ON s.Period = sp.Sales_Period GROUP BY s.Salesman_Id
╔═════════════╦═════════════╗ ║ SALESMAN_ID ║ TOTAL_SALES ║ ╠═════════════╬═════════════╣ ║ Johnny ║ 11800 ║ ║ Pauline ║ 18800 ║ ║ Rowan ║ 13400 ║ ╚═════════════╩═════════════╝