Calculate contribution margin

Question

I'd like to use subqueries and calculation at the same time for my sql project I have a table A where has the following information:

Table A

Month_revenue Income Cost
-------------------------
Jan           100    50
Feb           90     60
Mar           80     40

And I'd like to find the contribution margin for Jan, Feb and the difference of the contribution margins between Jan and Feb. Can I do that in one query and how?

The display should have the following format:

Jan      Feb       Mar         Jan/Feb            Feb/Mar
---------------------------------------------------------------
100-50   90 - 60  80-40  (100-50) - (90-60)  (90-60) - (80-40)

Thanks!

Define `Contribution Margin`? is just the number from `Income - Cost` or you want a string like `100 - 50`? Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, May 13 '16 at 14:04
So you want one result row with five columns, rather then five result rows? And there are guaranteed to be only three records, one for January, one for February, and one for March in your table? — Thorsten Kettner, May 13 '16 at 14:06
You are looking for pivot table ... Here is a good example for it http://stackoverflow.com/a/26297463/3470178 — Juan Carlos Oropeza, May 13 '16 at 14:08
Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Juan Carlos Oropeza, May 13 '16 at 14:08
Which RDBMS? But note that issues of data display are generally best handled in application level code. — Strawberry, May 13 '16 at 14:08
contribution margin = revenue - cost and yes, one result row with five columns instead of five result rows and one for Jan, one for Feb and on March in the table — Kane, May 13 '16 at 14:34

score 0 · Answer 1 · answered May 13 '16 at 14:53

You can simply use three selections to the table:

select 
  jan.income - jan.cost as jan,
  feb.income - feb.cost as feb,
  mar.income - mar.cost as mar,
  (jan.income - jan.cost) - (feb.income - feb.cost) as jan_feb,
  (feb.income - feb.cost) - (mar.income - mar.cost) as feb_mar
from
  (select * from mytable where Month_revenue = 'Jan') jan
cross join
  (select * from mytable where Month_revenue = 'Feb') feb
cross join
  (select * from mytable where Month_revenue = 'Mar') mar;

Or you can aggregate conditionally:

select
  sum(case when Month_revenue = 'Jan' then income - cost end) as jan,
  sum(case when Month_revenue = 'Feb' then income - cost end) as feb,
  sum(case when Month_revenue = 'Mar' then income - cost end) as mar,
  sum(case when Month_revenue = 'Jan' then income - cost end) -
  sum(case when Month_revenue = 'Feb' then income - cost end) as jan_feb,
  sum(case when Month_revenue = 'Feb' then income - cost end) -
  sum(case when Month_revenue = 'Mar' then income - cost end) as feb_mar
from mytable;

Calculate contribution margin

1 Answers1