Monthly sales in MySQL

-- ---------------------------------------------------------------------------
-- Monthly sales
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=1222
-- ---------------------------------------------------------------------------

-- The pivot logic that reports expenses by month and year works just as well
-- for sales. In the usual order entry system with customers, orders, products,
-- orders, order items and payments (leaving out irrelevant details) ...

drop table if exists items, payments, products, orders, customers;

create table customers(cust_id int primary key) engine=innodb;

create table orders(
  order_id int primary key,
  cust_id int,
  order_date date,
  foreign key(cust_id) references customers(cust_id)  -- ORDER->CUST FK
) engine=innodb;

create table products(prod_id int primary key) engine=innodb;

create table items(
  item_id int primary key,
  order_id int,
  prod_id int,
  qty int,
  foreign key(order_id) references orders(order_id),  -- ITEM->ORDER FK
  foreign key(prod_id) references products(prod_id)   -- ITEM->PROD FK
) engine=innodb;

create table payments(
  pmt_id int primary key,
  order_id int,
  amount decimal(8,2),
  foreign key(order_id) references orders(order_id)   -- PAYMENT->ORDER FK
) engine=innodb;

-- ... this query pivots to summarise sales by month and year:

select
  IfNull(Year,'Totals') Year,
  Format( `Jan`,2) as Jan,
  Format( `Feb`,2)as Feb,
  Format( `Mar`,2)as Mar,
  Format( `Apr`,2)as Apr,
  Format( `May`,2)as May,
  Format( `Jun`,2)as Jun,
  Format( `Jul`,2)as Jul,
  Format( `Aug`,2)as Aug,
  Format( `Sep`,2)as Sep,
  Format( `Oct`,2)as Oct,
  Format( `Nov`,2)as Nov,
  Format( `Dec`,2)as `Dec`,
  Qty AS Count,
  Format(Yrly,2) as 'Yrly Total'
from (
  select
    year(o.order_date) AS 'Year',
    sum(if(month(order_date)= 1, amount, 0)) as Jan,
    sum(if(month(order_date)= 2, amount, 0)) as Feb,
    sum(if(month(order_date)= 3, amount, 0)) as Mar,
    sum(if(month(order_date)= 4, amount, 0)) as Apr,
    sum(if(month(order_date)= 5, amount, 0)) as May,
    sum(if(month(order_date)= 6, amount, 0)) as Jun,
    sum(if(month(order_date)= 7, amount, 0)) as Jul,
    sum(if(month(order_date)= 8, amount, 0)) as Aug,
    sum(if(month(order_date)= 9, amount, 0)) as Sep,
    sum(if(month(order_date)=10, amount, 0)) as Oct,
    sum(if(month(order_date)=11, amount, 0)) as Nov,
    sum(if(month(order_date)=12, amount, 0)) as `Dec`,
    count(p.prod_id) AS Qty,
    round(sum($.amount),2) AS Yrly
  from orders o
  join items    i on o.order_id = i.order_id
  join products p on i.prod_id = p.prod_id
  join payments $ on o.order_id = $.order_id
  group by year
  with rollup
) sums ;