SQL - calculate percentage increase in values of a numeric column

Answer 1

In order to calculate trends, you will need to store historical data of the values you are interested in.

Answer 2

Should be useful if you have a integer column with the year+month (YYYYMM)

cast(
     cast(year(date) as nvarchar) + 
     replicate('0',2-len(cast(month(date) as nvarchar))) +
     cast(month(date) as nvarchar)
as int)

Then you only have to group, get the previous value and calculate.

select yearmonth, 
       sum(t.orders), 
       (sum(t.orders)/(select top 1 sum(orders) 
                       from table 
                       where yearmonth<t.yearmonth 
                       group by yearmonth 
                       order by yearmonth desc)-1)*100. as pct 
from table t 
group by yearmonth

T-SQL syntax, hope that you can use it in sqlite

PS: A division by zero check must be done to avoid further problems.

Answer 3

Not sure about the column Date, but if there is only 1 row per month, the answer could be (I don't have sqlite, but I think this answer is ANSI sql):

select t.date
,      t.month
,      t.orders
,      case when t2.orders = 0 then
          99999.0
       else
          100.0 * (t.orders - t2.orders) / t2.orders
       end  "increase"
from   t
left join t t2 on t2.month = t.month - 1
order by t.month

Answer 4

I'm not sure if sqllite allows for variables, but this is the way I do it in MySQL. I've just tested this and worked like a charm: printed 100% for a 10 to 20 increase, -13.04% for 46 to 40 decrease.

Hopefully it's similar in sqllite:

SELECT
  CONCAT(
    FORMAT(
      IF(@prev AND SUM(orders) <> @prev,
         -1 * 100 * (1 - SUM(orders) / @prev),
       0
        )
      , 2)
  , '%') AS variation,
    @prev := SUM(orders) AS orders
FROM
    (SELECT @prev := NULL) init,
    product
GROUP BY
  id_product
ORDER BY
  month

Of course, you'll need to tweak output as you need. This currently formats the precentages to 2 decimals and appends a "%" at the end.