Artful Common Queries

Monthly expenses

from the Artful Common Queries page

You have four tables to track revenue and expenses—bankaccount, cash, accountitem, accountcategory:

drop table if exists accountitem,accountcategory,bankaccount,cash;
create table accountitem(
  itemid int primary key auto_increment,itemname char(32),itemcatid int
);
create table accountcategory(
  categoryid int primary key auto_increment,categoryname char(32),isexpense bool
);
create table bankaccount(
  id int auto_increment primary key,amount decimal(12,2),itemid int,entrydate date
);
create table cash(
  id int auto_increment primary key,amount decimal(12,2),itemid int,date date
);

You need monthly expense totals. The solution uses two query patterns—

sum across a join of cash and bank tables for totals
pivot on month to break out monthly totals

select
  if(month=1, m.Amt+n.Amt,0 ) As `Jan`,  -- pivot by month
  if(month=2, m.Amt+n.Amt,0 ) As `Feb`,
  if(month=3, m.Amt+n.Amt,0 ) As `Mar`,
  if(month=4, m.Amt+n.Amt,0 ) As `Apr`,
  if(month=5, m.Amt+n.Amt,0 ) As `May`,
  if(month=6, m.Amt+n.Amt,0 ) As `Jun`,
  if(month=7, m.Amt+n.Amt,0 ) As `Jul`,
  if(month=8, m.Amt+n.Amt,0 ) As `Aug`,
  if(month=9, m.Amt+n.Amt,0 ) As `Sep`,
  if(month=10,m.Amt+n.Amt,0 ) As `Oct`,
  if(month=11,m.Amt+n.Amt,0 ) As `Nov`,
  if(month=12,m.Amt+n.Amt,0 ) As `Dec`
from (
  select Month(a.Date) As Month, sum(a.Amount) as Amt 
  from cash            a
  join accountitem     b on a.itemid=b.itemid 
  join accountcategory c on b.ItemCatID=c.CategoryID 
  where c.IsExpense=1 
  Group By Month
) As m join (                            -- join cash & bank data
  select Month(a.EntryDate) As  Month, sum(a.Amount ) AS Amt
  from BankAccount     a
  join accountitem     b on a.itemid=b.itemid 
  join accountcategory c on b.ItemCatID=c.CategoryID 
  where c.IsExpense=1
  Group By Month
) As n using(Month);

Last updated 16 Feb 2010