-- drop table if exists benfordtemp;
-- drop table if exists valtemp;

-- table of the benford values (see Wikipedia on Benford's Law
create table benfordtemp (
  d int,
  p     double
);

insert into benfordtemp 
values 
(1,	30.1),
(2,	17.6),
(3,	12.5),
(4,	9.7),
(5,	7.9),
(6,	6.7),
(7,	5.8),
(8,	5.1),
(9,	4.6);

-- table of first digit counts in populations 
create table valtemp (
 k int
);

insert into valtemp
  select substring(population, 1, 1) from Country;

set @total = (select count(*) from valtemp);


plot
  axislabels, very light blue bars,  very big light blue hortick
  with
    title "Benford's Law Applied to Country Populations"
    page half
    format y decimal "#'%'"
    title x "First Digit of Population Value (1-9)"
    legend at 550 275
    no sides
    no ticks x
select 
   k, (count(*) / @total) * 100 as 'Actual Percentage', 
   (select p from benfordtemp where d=k) as "Benford's Law Prediction"

  from valtemp
  group by k
  having k > 0;

report 
  plain, decimal "#.0'%'"
  margins 20
select 
  k as 'Digit', 
  count(*) as 'Count', 
  (count(*) / @total) * 100 as 'Percent',
   (select p from benfordtemp where d=k) as 'Benford'
  from valtemp
  group by k
  having k > 0;