Talal qadoummi - 2 years ago 97
SQL Question

# Calculate the variation of values in amazon redshift database

I'm trying to calculate the variation of two values between current month and the previous one.
let's say I have a total calls in different months and and want to have the variation for each month from the previous one.
U have a table contains vendor, month and calls in each month
I have tried the following query nut it gives a wrong results for the same vendor if there is no data in the previous month

``````  select vendor,
nvl(round(sum(calls),0),0.00) as "total_calls",
nvl((((lag(CAST(sum(calls) AS decimal) ,0) over(order by month)) -
(lag(CAST(sum(calls) AS DECIMAL),1) over(order by month))) /
(lag(CAST(sum(calls) AS DECIMAL),1) over(order by month))), 0) as tot_calls_variation
from table_summary
group by full_month,vendor
order by month,vendor
``````

the lag() function returns the row by given index.but this gives wrong results since the variation is calculated by rows and not by each vendor
wondering if there is any other way to do so ? thanks

It's difficult to say without seeing your data and desired results, but perhaps you might be better of just going with a self join instead of window functions:

``````SELECT
month_summary.vendor,
month_summary.calls,
month_summary.calls - prev_month_summary.calls / prev_month_summary.calls) as tot_calls_variation
FROM
(SELECT vendor, full_month, sum(calls) as calls FROM table_summary GROUP BY vendor, full_month) as month_summary
INNER JOIN (SELECT vendor, full_month, sum(calls) as calls FROM table_summary GROUP BY vendor, full_month) as prev_month_summary ON
month_summary.vendor = prev_month_summary.vendor AND
month_summary.full_month - 1 = prev_month_summar.full_month
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download