Mysql each row sum

Question

How can I get result like below with mysql?

> +--------+------+------------+
> | code   | qty  | total      |
> +--------+------+------------+
> |    aaa |   30 |         75 |
> |    bbb |   20 |         45 |
> |    ccc |   25 |         25 |
> +--------+------+------------+

total is value of the rows and the others that comes after this.

Should the ordering always be by `code`? – Joachim Isaksson Sep 14 '14 at 14:39 — Joachim Isaksson, Sep 14 '14 at 14:39

score 3 · Accepted Answer · answered Sep 14 '14 at 14:39

3

You can do this with a correlated subquery -- assuming that the ordering is alphabetical:

select code, qty,
       (select sum(t2.qty)
        from mytable t2
        where t2.code >= t.code
       ) as total
from mytable t;

SQL tables represent unordered sets. So, a table, by itself, has no notion of rows coming after. In your example, the codes are alphabetical, so they provide one definition. In practice, there is usually an id or creation date that serves this purpose.

answered Sep 14 '14 at 14:39

Gordon Linoff

1,242,037
58
646
786

@ozer . . . This query only references one table. – Gordon Linoff Sep 14 '14 at 14:43
It has O(n^2) complexity. – George Sovetov Sep 14 '14 at 14:47

Michał Powaga · Answer 2 · 2014-09-14T16:24:13.613

2

I would use join, imho usually fits better.

Data:

create table tab (
  code varchar(10),
  qty int
);

insert into tab (code, qty)
select * from (
  select 'aaa' as code, 30 as qty union
  select 'bbb', 20 union
  select 'ccc', 25
) t

Query:

select t.code, t.qty, sum(t1.qty) as total
from tab t
join tab t1 on t.code <= t1.code
group by t.code, t.qty
order by t.code

The best way is to try both queries (my and with subquery that @Gordon mentioned) and choose the faster one.

Fiddle: http://sqlfiddle.com/#!2/24c0f/1

edited Sep 14 '14 at 16:24

answered Sep 14 '14 at 15:05

Michał Powaga

22,561
8
51
62

Yours is going to be faster because its using a join whereas Gordon's is a correlated subquery – John Ruddell Sep 14 '14 at 15:35

score 0 · Answer 3 · edited May 23 '17 at 10:33

0

Consider using variables. It looks like:

select code, qty, (@total := ifnull(@total, 0) + qty) as total
    from your_table
    order by code desc

...and reverse query results list afterward.

If you need pure SQL solution, you may compute sum of all your qty values and store it in variable.

Also, look at: Calculate a running total in MySQL

edited May 23 '17 at 10:33

Community

1
1

answered Sep 14 '14 at 14:46

George Sovetov

4,942
5
36
57

it did not helped me, sorry. – ozer Sep 14 '14 at 14:55
@ozer, that's no more than pseudocode. The simplest solution is probably to calculate running total in your program, not by means of SQL. – George Sovetov Sep 14 '14 at 15:02

Mysql each row sum

3 Answers3