On Wed, Jul 29, 2009 at 8:34 AM, Adler, Eliedaat<[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
