Question 183756 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get the sum of a current column with a previous column in MySQL

I have a query that gets quantity 1, value 1 , total 1 what I am trying to get is the following result

quantity 1, value 1, total 1, sum quantity,  sum total / sum quantity as value summ, sum total
80 - 3.8 - 304 , 80 - 3.8 - 304
40 - 3.75 - 150, 120 - 3.783 - 454
40 -  3.8 -  152, 160 - 37875 - 606

that is to say, to be able to obtain the sum of the total, (total 1 + previous total sum)
to then do the division with quantity 1

I have tried using

SELECT 
@prev:= @prev + total 1 as column1,  @prev / quantity 1   as value
from table;

but I don’t get the expected results

use MySQL 5.6 and windows S.O

Tags:

3

Answers


  1. 0

    you can use user-defined variables to achieve the desired result. Here’s an example query that calculates the cumulative sum and division:

    SET @prev := 0;
SELECT
  quantity,
  value,
  total,
  @prev := @prev + total AS cumulative_total,
  (@prev / quantity) AS value_summ,
  @prev AS sum_total
FROM
  table_name;

    Make sure to replace table_name with the actual name of your table.

    This query initializes the @prev variable to 0. Then, it selects the columns quantity, value, and total, and performs calculations using the user-defined variable @prev to calculate the cumulative total and the division. The results are displayed in the columns: quantity, value, total, cumulative_total, value_summ, and sum_total.

    Note that user-defined variables are session-specific, so the @prev variable will retain its value within the same session. If you run this query multiple times in the same session, make sure to reset the variable by executing SET @prev := 0; again before running the query.

    Login or Signup to reply.
  2. 0

    Without using an order by clause, the results of your @prev value would be indeterministic. You need to have a column (id, timestamp…) that uniquely identifies the order of your rows, so the SQL can determine correctly what the previous value is.

    consider the following example based on your sample data:

    set @sum_quantity =0;
set @sum_total =0;
set @prev_tot_sum =0;

select *,
  @sum_quantity := @sum_quantity + quantity1 as sum_quantity,
  @sum_total := @sum_total + total1 as sum_total,
  @sum_total / @sum_quantity as 'sum total / sum quantity',
  @sum_total / quantity1 as 'value'
from table_name
order by id

    demo

    Login or Signup to reply.
  3. 0

    If you are using MySQL 5.6 and want your answer in one single query

    SELECT 
id, 
quantity1,
value1,
@sum_quantity:=@sum_quantity+quantity1 AS sumQuantity,
@sum_total:=@sum_total+total1 AS sumTotal,
@sum_total/@sum_quantity AS sumTotalBySumQuantity,
@sum_total/quantity1 AS val
FROM 
(SELECT 
  *,
  @sum_quantity:=0,
  @sum_total:=0, 
  @prev_tot_sum:=0
FROM
  table_name t ) a

    This should work

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search