SQL expression to perform a calculation

[springboardjg]

Hi



My SQL query returns the following table.

Springboard_Benchmark | MoMCurrentMonthTotal | MoMPreviousMonthTotal
Central | 28885 | 27648
National | 13233457 | 12141305

Here is the SQL query.

SELECT tbl_SpringboardBenchmarks.Springboard_Benchmark, Sum(qry_MoM_CurrentMonth.MoMCurrentMonth) AS MoMCurrentMonthTotal, Sum(qry_MoM_PreviousMonth.MoMPreviousMonth) AS MoMPreviousMonthTotal
FROM tbl_SpringboardBenchmarks INNER JOIN 
(qry_MoM_CurrentMonth INNER JOIN qry_MoM_PreviousMonth ON qry_MoM_CurrentMonth.Average_Grouping = qry_MoM_PreviousMonth.Average_Grouping) 
ON tbl_SpringboardBenchmarks.Average_Grouping = qry_MoM_CurrentMonth.Average_Grouping
GROUP BY tbl_SpringboardBenchmarks.Springboard_Benchmark;

What I want is another column in the table above like this.

Springboard_Benchmark | MoMCurrentMonthTotal | MoMPreviousMonthTotal | %Change
Central | 28885 | 27648 | 4.47%
National | 13233457 | 12141305 | 9.00%

So I need to put the calculation (MoMCurrentMonthTotal)/(MoMPreviousMonthTotal)-1 into my SQL query.

Becuase I have already used the SUM query I am not sure how to do it without getting an error. Any ideas?


Many thanks

James

[orange]

You can try this (totally untested)

SELECT Benchmark
,MoMCurrentMonthTotal
,MoMPreviousMonthTotal
,(MoMCurrentMonthTotal - MoMPreviousMonthTotal) *100 as PercentChange
FROM
(
SELECT tbl_SpringboardBenchmarks.Springboard_Benchmark as Benchmark
, Sum(qry_MoM_CurrentMonth.MoMCurrentMonth) AS MoMCurrentMonthTotal
, Sum(qry_MoM_PreviousMonth.MoMPreviousMonth) AS MoMPreviousMonthTotal
FROM tbl_SpringboardBenchmarks
INNER JOIN
(qry_MoM_CurrentMonth INNER JOIN qry_MoM_PreviousMonth ON qry_MoM_CurrentMonth.Average_Grouping = qry_MoM_PreviousMonth.Average_Grouping)
ON tbl_SpringboardBenchmarks.Average_Grouping = qry_MoM_CurrentMonth.Average_Grouping
GROUP BY tbl_SpringboardBenchmarks.Springboard_Benchmark
);