CODEWITHSUNDEEP
mysqlcumulative-sum
Josef
Josef

Reputation: 2726

MySQL cumulative sum gives wrong result

Here is the sample of my table with some sample data-

The strange things happens while making cumulative sum of difference between columns gorivo.PovratKM and gorivo.PolazakKM and same for gorivo.UkupnoGorivo.

The cumulative sums are in column SumUkKM for difference between gorivo.PovratKM and gorivo.PolazakKM and for cumulative sum for gorivo.UkupnoGorivo is column SumGorivo.

The output should be something like:

+-------------+------------+-------------+------------+  
| Polazak KM  | Povratal KM| Prijedeno KM|  SumUkKM   |
+-------------+------------+-------------+------------+  
| 814990      | 816220     |      1230   |    1230    |                 
+-------------+------------+-------------+------------+
| 816220      | 817096     |      876    |    2106    |                 
+-------------+------------+-------------+------------+
| 817096      | 817124     |      28     |    2134    |                 
+-------------+------------+-------------+------------+
| 817124      | 818426     |      1302   |    3436    |                 
+-------------+------------+-------------+------------+

What I'm doing wrong in my query?

Upvotes: 0

Views: 197

Answers (1)

McNets
McNets

Reputation: 10807

MySql allows to declare variables in the sql sentence, (select @SumUkGorivo := 0, @SumUkKM := 0) x the CROSS JOIN allows to calculate its value for each row of the other table.

Using variables, you can, for example set reset points or partitions on the same way than SUM() OVER (PARTITION BY is used by other dmbs like SQL or Postgres.

SELECT 
   y.`PolazakKM`, y.`PovratakKM`,
   @SumUkGorivo := @SumUkGorivo + `UkupnoGorivo` as SumUkGorivo,
   @SumUkKM := @SumUkKM + (y.`PovratakKM` - y.`PolazakKM`) as SumUkKM
FROM 
    (select @SumUkGorivo := 0, @SumUkKM := 0) x,
    (select gorivo.`PolazakKM`, gorivo.`PovratakKM`, gorivo.`UkupnoGorivo`
     from gorivo WHERE gorivo.`IDVozilo` = 131
     order by `DatumT`) y
;

Upvotes: 2

Related Questions