SUM IF totals help

[Andrew Adcock]

Hello all,

I am trying to build a personal budget with php and mysql. I have a few forms that populate 4 sql tables (1 table handels the main categories, 1 the subcategories, 1 handles the input of expendatures and the last one stores the budget goal amount)

currently I have a report page that spits out a table for the months selected with each category and subcategory showing with 3 cells for each subcategory by month. 1 for the total input of expendatures; 1 for the bugeted goal and 1 for the difference between the two.

Now I want to total each row and total each column. (The columns are arranged by month and the rows by subcategory).

here is the query I tried:

SELECT category_item
, SUM( IF( select_month IN ( 01 ) , budget_amount + expendature, 0 ) ) AS `JAN` 
, SUM( IF( select_month IN ( 02 ) , budget_amount, 0 ) ) AS `FEB` 
, SUM( IF( select_month IN ( 03 ) , budget_amount, 0 ) ) AS `MAR` 
, SUM( budget_amount) AS total 
FROM jos_chronoforms_bdgtbymonth INNER JOIN jos_chronoforms_budgetinfo
USING ( cat_id )INNER JOIN jos_chronoforms_exsubcategories USING ( cat_id )
GROUP BY category_item

but it is not returning the correct numbers??

Any ideas how to get the totals?

Also, here is my sql table sturctures

-- --------------------------------------------------------

--
-- Table structure for table `jos_chronoforms_bdgtbymonth`
--

CREATE TABLE IF NOT EXISTS `jos_chronoforms_bdgtbymonth` (
  `bdgtgoal_id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) NOT NULL,
  `recordtime` varchar(255) NOT NULL,
  `budget_amount` varchar(255) NOT NULL,
  `select_year` varchar(255) NOT NULL,
  `select_month` varchar(255) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `itemList` int(11) NOT NULL,
  PRIMARY KEY (`bdgtgoal_id`),
  KEY `bdgtgoal_id` (`bdgtgoal_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

-- --------------------------------------------------------

--
-- Table structure for table `jos_chronoforms_budgetinfo`
--

CREATE TABLE IF NOT EXISTS `jos_chronoforms_budgetinfo` (
  `bdgt_id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) NOT NULL,
  `recordtime` varchar(255) NOT NULL,
  `expname` varchar(255) NOT NULL,
  `expendature` varchar(255) NOT NULL,
  `date_3` varchar(255) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `itemList` int(11) NOT NULL,
  PRIMARY KEY (`bdgt_id`),
  KEY `bdgt_id` (`bdgt_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Table structure for table `jos_chronoforms_excategories`
--

CREATE TABLE IF NOT EXISTS `jos_chronoforms_excategories` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) NOT NULL,
  `uid` varchar(255) NOT NULL,
  `recordtime` varchar(255) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Table structure for table `jos_chronoforms_exsubcategories`
--

CREATE TABLE IF NOT EXISTS `jos_chronoforms_exsubcategories` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_item` varchar(255) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `uid` varchar(255) NOT NULL,
  `recordtime` varchar(255) NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=76 ;

Edited 1 time(s). Last edit at 02/03/2010 10:48AM by Andrew Adcock.