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