-- http://dbentrance.com/demo/pareto.sql
-- Example for the Entrance blog:  http://dbentrance.com/blog/

-- drop table if exists t;
-- drop table if exists t2;

-- the sample data
create table t (what varchar(255), sales double);
insert into t values
  ('bagels', 20), ('donuts', 10), ('coffee', 80), ('prune danish', '40'), 
   ('scones', 2.5), ('toast', 5);

-- accumulate totals
set @sum = 0.0;
create table t2
select what, sales, @sum := sales+@sum as accum
  from t
  order by sales desc
;

-- calculate percentages
set @total = (select sum(sales) from t2);
alter table t2 add column accum_pct double after accum;
alter table t2 add column sales_pct double after accum;
update t2
  set accum_pct = (accum/@total) * 100.0,
  sales_pct =     (sales/@total) * 100.0;

-- make the plot
plot
     axislabels, light gray bar,      
     red line, black filled circle, datalabels
   with
     scale y 0 100 25
     format y decimal "#'%'"
     no sides
     title " "
     title y "Percentage of Sales"
     title x "Item"
select
    what,   sales_pct, 
    accum_pct, accum_pct, concat(format(accum_pct, 1), '%    ')
  from t2
  order by sales desc
;