Re: [sqlite] Subtotal SQL

On Wed, Jul 29, 2009 at 8:34 AM, Adler, Eliedaat<Eadler@xxxxxxx> wrote:
>
> SQL/sqlite challenge  for all:
>
>
> 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
>
>
> Sort by filename, owner
> Name     Size     Sum
> A           70        70
> A          100      170
> B           90       260
> C          80        340
>
> User Function/Aggregates welcome!
> thanks
> Eliedaat Adler
>
>


sqlite> CREATE TABLE a (a_id INTEGER PRIMARY KEY, a_desc TEXT, a_tot INTEGER);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('bla', 20);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('foo', 30);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('bar', 13);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('baz', 132);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('qux', 42);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('gam', 12);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('fro', 87);
sqlite> INSERT INTO a (a_desc, a_tot) VALUES ('nic', 119);
sqlite> .h on
sqlite> .m col
sqlite> SELECT * FROM a;
a_id        a_desc      a_tot
----------  ----------  ----------
1           bla         20
2           foo         30
3           bar         13
4           baz         132
5           qux         42
6           gam         12
7           fro         87
8           nic         119
sqlite> SELECT a1.a_desc, a1.a_tot, SUM(a2.a_tot) total
   ...> FROM a a1, a a2
   ...> WHERE a1.a_tot <= a2.a_tot OR
   ...>   (a1.a_desc=a2.a_desc AND a1.a_tot = a2.a_tot)
   ...> GROUP BY a1.a_desc, a1.a_tot
   ...> ORDER BY a1.a_tot DESC, a1.a_desc DESC;
a_desc      a_tot       total
----------  ----------  ----------
baz         132         132
nic         119         251
fro         87          338
qux         42          380
foo         30          410
bla         20          430
bar         13          443
gam         12          455
sqlite>


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