-- Exercise 3.18a
create table buckets (bucket int, primary key(bucket));
insert into buckets select distinct floor(dollars/500) from orders;
select bucket*500 as "range-start", sum(dollars) from orders o, buckets b
where floor(o.dollars/500) = b.bucket
group by b.bucket;
drop table buckets;
-- Exercise 3.19b
--cumulative histogram of dollars figures
--the median is where sum(ct) = (select count(*)/2 from orders)
select h2.dollars, sum(ct) from
(select dollars, count(dollars) ct from orders group by dollars) h1,
(select dollars from orders group by dollars) h2
where h1.dollars <= h2.dollars -- over all h1 vals <= h2 vals
group by h2.dollars;
order by h2.dollars; -- just for reporting
--the median in one query, where F here is the cumulative histogram
--computed above
select dollars from
(select h2.dollars, sum(h1.ct) ct from
(select dollars, count(dollars) ct from orders group by dollars) h1,
(select dollars from orders group by dollars) h2
where h1.dollars <= h2.dollars -- over all h1 vals <= h2 vals
group by h2.dollars) F
where ct = (select min(ct1) from
(select h2.dollars, sum(ct1) ct1 from
(select dollars, count(dollars) ct1 from orders group by dollars) h1,
(select dollars from orders group by dollars) h2
where h1.dollars <= h2.dollars -- over all h1 vals <= h2 vals
group by h2.dollars) F
where ct1 >= (select count(*)/2 from orders));