AnsweredAssumed Answered

Using Execute SQL to do calculation of values based on different columns criteria

Question asked by camcorp on Jun 2, 2017
Latest reply on Jun 2, 2017 by fmpdude

Hi Community

I'm doing the following calculation from a table of a journal book.

the table structure is as follows

rdGPZ.png

and the result of the query should be

rMhFS.png

For Month 1 the total amount  based on column LEVEL1 "Gross Margin' should be  400  [1500-1100) divided by the total amount based on column LEVEL0 "Income" [1500] , the result for Month1 should be 400/1500=0,27

My SQL Statement is as follow

 

SELECT Month,
  SUM
(CASE WHEN Level1 = 'Gross Margin' THEN Amount END)/
  SUM
(CASE WHEN Level0 = 'Income' THEN Amount END) as Ratio
FROM My Table
GROUP BY Month;

 

Something is not working, please help and thanks in advance

 

Outcomes