Skip navigation links

MySQL Forums :: Newbie :: SUM IF totals help


Advanced Search

SUM IF totals help
Posted by: Andrew Adcock ()
Date: February 03, 2010 10:24AM

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.

Options: ReplyQuote


Subject Written By Posted
SUM IF totals help Andrew Adcock 02/03/2010 10:24AM
Re: SUM IF totals help Andrew Adcock 02/03/2010 02:31PM
Re: SUM IF totals help laptop alias 02/03/2010 03:19PM
Re: SUM IF totals help Andrew Adcock 02/03/2010 07:57PM
Re: SUM IF totals help laptop alias 02/04/2010 04:10AM
Re: SUM IF totals help Andrew Adcock 02/04/2010 06:12AM
Re: SUM IF totals help laptop alias 02/04/2010 07:57AM
Re: SUM IF totals help Andrew Adcock 02/04/2010 08:35AM
Re: SUM IF totals help laptop alias 02/04/2010 09:12AM
Re: SUM IF totals help Andrew Adcock 02/04/2010 10:02AM
Re: SUM IF totals help Andrew Adcock 02/04/2010 10:19AM
Re: SUM IF totals help laptop alias 02/04/2010 10:26AM
Re: SUM IF totals help Andrew Adcock 02/04/2010 11:06AM
Re: SUM IF totals help laptop alias 02/04/2010 12:52PM
Re: SUM IF totals help Andrew Adcock 02/04/2010 07:12PM
Re: SUM IF totals help Andrew Adcock 02/04/2010 06:53PM
Re: SUM IF totals help Francis Regman 02/04/2010 02:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.