Given a table with a (parent, child) PK, which carries a size "data" column,
I'd like to aggregate the sum of the size per-parent, from 1KB, to each
range
from low to 8*low (i.e. [low, 8*low)), but w/o having to write my hand all
possible
combination, with extra points for formatting the range bounds in KB, MB,
GB as appropriate.
This is not too dissimilar to my recent question about the "thousand
separator",
but a bit more complex. Any help of formulating this query generically
(using a CTE?)
would be appreciated.
Thanks, --DD
C:\Users\ddevienne>sqlite3.18.0rc
SQLite version 3.18.0 2017-03-06 20:44:13
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select printf('%,d', 1001);
1,001
sqlite> select 'Thank you Richard';
Thank you Richard
sqlite> create table t (p, c, s integer, primary key (p, c));
sqlite> insert into t values
...> (1, 1, 4),
...> (1, 2, 900),
...> (2, 1, 12000),
...> (2, 2, 24000),
...> (3, 1, 123456),
...> (3, 2, 123456),
...> (3, 3, 123456);
sqlite> select p, count(*), sum(s) from t group by p order by 3 desc;
3|3|370368
2|2|36000
1|2|904
sqlite> with u(v) as (select sum(s) from t group by p)
...> select '[1B, 1KB)', count(*) from u where v < 1024
...> union all
...> select '[1KB, 8KB)', count(*) from u where 1024 <= v and v < 8*1024
...> union all
...> select '[8KB, 64KB)', count(*) from u where 8*1024 <= v and v <
8*8*1024
...> union all
...> select '[64KB, 512KB)', count(*) from u where 8*8*1024 <= v and v <
8*8*8*1024
...> ;
[1B, 1KB)|1
[1KB, 8KB)|0
[8KB, 64KB)|1
[64KB, 512KB)|1
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
