Re: [sqlite] Subtotal SQL

Adler, Eliedaat <Eadler@xxxxxxx> wrote:
> I have a table with many columns describing objects on a disk.
>
>    filename, date, owner, size
>    A            12    MO    100
>    B            13   JAK    90
>    C            10   MO    80
>    A            13   LU      70
>
> I have many different ways of ordering these objects. Usually using
> several sort parameters.
>
> I need a running sum of size that works regardless of what order the
> objects are in.
>
> Sort by date,owner
> Name     Size    Sum
> C            80       80
> A          100      180
> B           90       270
> A          70      340

You'll be better off calculating the running total in the host 
application, as you step over the resultset. But, if you insist:

select filename, size,
    (select sum(t2.size) from mytable t2
     where t2.date <= t1.date and (t2.date < t1.date or t2.owner < 
t1.owner) Sum
from mytable t1
order by date, owner;

Igor Tandetnik 



_______________________________________________
sqlite-users mailing list
sqlite-users@xxxxxxxxxx
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users