Hello,
I'm currently working on an application for monitoring financial activities.
I need to differentiate between two main categories: income and outlay, and sub catgories of those two main categories.

In order to achive that I've create 4 tables
prefix_income - a table for all the activities under the income category
prefix_income_cat - a table for all the sub categories of the income categroy
prefix_outlay - a table for all the activities under the outlay category
prefix_outlay_cat - a table for all the sub categories of the outlay categroy

Other than been able to differentiate between incomes / outlays by subcategories, I have different payment types. These types are stattic so there is no need for an extra table for them.

The following sql statements are the ones that I've used to create the tables, not that there are some fileds that are irrlevant to my question so you should just ignore them.

Please note that because I'll need to preform a lot of summing up with this table I've add the field `balance` that saves the current balance in each activity so i wont have to use the sum function, but this makes insertation and removal a lot heavier, so I'm not sure if I should use this method or not.

Code MySQL:
CREATE TABLE IF NOT EXISTS `tazrim_cat_income` (
  `id` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tazrim_cat_outlay`
--
 
CREATE TABLE IF NOT EXISTS `tazrim_cat_outlay` (
  `id` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tazrim_income`
--
 
CREATE TABLE IF NOT EXISTS `tazrim_income` (
  `id` int(11) NOT NULL auto_increment,
  `date` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `customer` text NOT NULL,
  `cat` int(11) NOT NULL,
  `notes` text NOT NULL,
  `payment` int(11) NOT NULL,
  `redemption` int(11) NOT NULL,
  `refrence` int(11) NOT NULL,
  `sum` double NOT NULL,
  `cycle` int(11) NOT NULL,
  `cycle_num` int(11) NOT NULL,
  `bank` int(11) NOT NULL,
  `balance` double NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `active` tinyint(1) unsigned NOT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tazrim_outlay`
--
 
CREATE TABLE IF NOT EXISTS `tazrim_outlay` (
  `id` int(11) NOT NULL auto_increment,
  `date` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `customer` text NOT NULL,
  `cat` int(11) NOT NULL,
  `notes` text NOT NULL,
  `payment` int(11) NOT NULL,
  `redemption` int(11) NOT NULL,
  `refrence` int(11) NOT NULL,
  `sum` double NOT NULL,
  `cycle` int(11) NOT NULL,
  `cycle_num` int(11) NOT NULL,
  `bank` int(11) NOT NULL,
  `balance` double NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `active` tinyint(1) unsigned NOT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Generated using PhpMyAdmin

And here is some data so that my question could be better understood
Code MySQL:
--
-- Dumping data for table `tazrim_income`
--
 
INSERT INTO `tazrim_income` (`id`, `date`, `type`, `customer`, `cat`, `notes`, `payment`, `redemption`, `refrence`, `sum`, `cycle`, `cycle_num`, `bank`, `balance`, `customer_id`, `active`) VALUES
(1, 1236499200, 1, 'name', 3, '', 1, 1236499200, 12, 50, 0, 0, 1, 50, 1, 1),
(2, 1236499200, 2, 'name', 3, '', 1, 1236499200, 12, 60, 0, 0, 1, 60, 1, 1);
 
--
-- Dumping data for table `tazrim_outlay`
--
 
INSERT INTO `tazrim_outlay` (`id`, `date`, `type`, `customer`, `cat`, `notes`, `payment`, `redemption`, `refrence`, `sum`, `cycle`, `cycle_num`, `bank`, `balance`, `customer_id`, `active`) VALUES
(1, 1236499200, 1, 'name', 3, '', 1, 1236499200, 12, 50, 0, 0, 1, 50, 1, 1),
(2, 1236499200, 2, 'name', 3, '', 1, 1236499200, 12, 60, 0, 0, 1, 60, 1, 1);
Generated using PhpMyAdmin

Now what I want to get from my select query is the following data
sum: (the sum of all the activities in the rage with type = 1)';'(the sum of all the activities in the rage with type = 2)
balance: (*only if there is no better way than using the balance)
redemption: (the redemption field)
type: (the type field)

I've created a query that retrives those results but its very heavy and I believe that it can be majorly improved...
The query:
Code MySQL:
(select
	if(count(*) > 1, (select group_concat(`sum` separator ';') from (select sum(`ti`.`sum`) as `sum`, `type` from `tazrim_income` as `ti` where `ti`.`redemption` = 1236499200 group by(`ti`.`type`)) as `tbl`),(select group_concat(sum(`ti`.`sum`),''))) as `ti_sum`,
	sum(`ti`.`balance`) as `ti_balance`,
	`ti`.`redemption` as `ti_redemption`,
	`ti`.`type` as `ti_type`,
	0 as `type`
 
from
	`tazrim_income` as `ti`
where `ti`.`redemption`>= 1235894400 && `ti`.`redemption` <= 1238569199 && `active` = 1
group by(`ti`.`redemption`))
union
(select
	if(count(*) > 1, (select group_concat(`sum` separator ';') from (select sum(`to`.`sum`) as `sum`, `type` from `tazrim_outlay` as `to` where `to`.`redemption` = 1236499200 group by(`to`.`type`)) as `tbl`),(select group_concat(sum(`to`.`sum`),''))) as `ti_sum`,
	sum(`to`.`balance`) as `to_balance`,
	`to`.`redemption` as `to_redemption`,
	`to`.`type` as `to_type`,
	1 as `type`
from
	`tazrim_outlay` as `to`
where `to`.`redemption`>= 1235894400 && `to`.`redemption` <= 1238569199 && `active` = 1
group by(`to`.`redemption`))
order by `ti_redemption` asc

Thanks in advance,
Kfir