CODEWITHSUNDEEP
mysqlsql
ozer
ozer

Reputation: 345

Mysql each row sum

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.

Upvotes: 0

Views: 762

Answers (3)

Michał Powaga
Michał Powaga

Reputation: 23183

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

Upvotes: 2

George Sovetov
George Sovetov

Reputation: 5238

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

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.

Upvotes: 3

Related Questions