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.

Answers


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

Output:

╔═════════════╦═════════════╗
║ SALESMAN_ID ║ TOTAL_SALES ║
╠═════════════╬═════════════╣
║ Johnny      ║       11800 ║
║ Pauline     ║       18800 ║
║ Rowan       ║       13400 ║
╚═════════════╩═════════════╝
See this SQLFiddle

Need Your Help

Xquery: Dividing the attribute value by spaces

xpath xquery database xpath-2.0

So I'm trying to get the country codes of this river.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.