MySQL Forums
Forum List  »  Newbie

Re: cumulative query grouped by createdate,warehouse,country and location
Posted by: Peter Brawley
Date: October 05, 2018 09:23AM

> a query that trigger the first dataset-example

Unclear.

Apologies, the example I pasted in is incorrect for subtotals. This is correct ...

drop table if exists t;
create table t( id int, a char(1), v int);
insert into t values(1,'a',10),(2,'b',20),(3,'b',30),(4,'c',40);

select x.a, x.v, sum(x.v) as cum
from t x
join t y on y.a = x.a and y.id <= x.id
group by x.id
order by x.a, x.id;

Notice that the table needs a PK that doesn't break the ordering by `a`.

But generally SQL isn't good for row-to-row calculations---it's set-based, and it's a query language rather than a full-fledged computer language. Common Table Expressions rectify that for some such problems, so if you are running 8.0.12, use a CTE. Otherwise you're best off doing the calculation in a fuller computer language (PHP, C#, Java, whatever your preferred flavour is) with a MySQL API.



Edited 1 time(s). Last edit at 10/05/2018 09:24AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: cumulative query grouped by createdate,warehouse,country and location
October 05, 2018 09:23AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.