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 ;
|
|