Monthly sales

from the Artful Common Queries page

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 ;

Last updated 29 Feb 2025