Subtracting groups of rows 1

[johnwm]

Is there a way of getting a 'difference' value on a row in a database. I have a db constructed thus:

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `cat` varchar(20) NOT NULL,
  `month1` int(11) NOT NULL,
  `month2` int(11) NOT NULL,
  `main` varchar(20) NOT NULL default ' ',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `cat`, `month1`, `month2`, `main`) VALUES(1, 'sales 1', 100, 150, 'sales');
INSERT INTO `test` (`id`, `cat`, `month1`, `month2`, `main`) VALUES(2, 'sales 2', 120, 170, 'sales');
INSERT INTO `test` (`id`, `cat`, `month1`, `month2`, `main`) VALUES(3, 'cost 1', 80, 100, 'cogs');
INSERT INTO `test` (`id`, `cat`, `month1`, `month2`, `main`) VALUES(4, 'cost 2', 90, 110, 'cogs');
INSERT INTO `test` (`id`, `cat`, `month1`, `month2`, `main`) VALUES(5, 'overhead 1', 20, 20, 'ohead');
INSERT INTO `test` (`id`, `cat`, `month1`, `month2`, `main`) VALUES(6, 'overhead 2', 22, 22, 'ohead');

The query to get totals by section looks like:

SELECT * FROM `test` where `main`='sales'
union
select ' ','Total Sales',sum(month1) as s1,sum(month2) as s2,' ' from test where `main`='sales' group by `main`
union 
SELECT * FROM `test` where `main`='cogs' 
union
select ' ','Direct costs',sum(month1) as c1,sum(month2) as c2,' ' from test where `main`='cogs' group by `main`
union
SELECT * FROM `test` where `main`='ohead' 
union
select ' ','Total OHead',sum(month1) as o1,sum(month2) as o2,' ' from test where `main`='ohead' group by `main`

I am looking for a way to generate a row for Gross Profit which is difference between the row

select ' ','Total Sales',sum(month1) as s1,sum(month2) as s2,' ' from test where `main`='sales' group by `main`

and the row

select ' ','Direct costs',sum(month1) as c1,sum(month2) as c2,' ' from test where `main`='cogs' group by `main`

with the aim of getting a row containing:
' ','Gross Profit','50','110',' '

I have checked the manual, but only found '-' operator. I also tried:

Select ' '.'Gross Profit',s1-c1,s2-c2,' '

and got a 'column s1 not found' error.

Will I be reduced to using negative numbers for costs, and doing a 'Sum' over all the rows, or is there a proper alternative?

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[johnwm]

Thank you for looking at this. I have decided to simply use a temp table to store the sum values so the problem has gone away.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[KarveR]

no temp table required, use localised variables. haven't had a huge chance to look, they do somthing odd on the second execution but look ok on the first:

SELECT * FROM `test` where `main`='sales'
union select ' ','Total Sales',@s1:=sum(month1) as s1,@s2:=sum(month2) as s2,' ' from test where `main`='sales' group by `main`
union SELECT * FROM `test` where `main`='cogs'
union select ' ','Direct costs',@c1:=sum(month1) as c1,@c2:=sum(month2) as c2,' ' from test where `main`='cogs' group by `main`
union SELECT * FROM `test` where `main`='ohead'
union select ' ','Total OHead',sum(month1) as o1,sum(month2) as o2,' ' from test where `main`='ohead'
union Select ' ','Gross Profit',@s1 - @c1,@s2 - @c2,'' from test group by `main`;
+----+--------------+--------+--------+-------+
| id | cat | month1 | month2 | main |
+----+--------------+--------+--------+-------+
| 1 | sales 1 | 100 | 150 | sales |
| 2 | sales 2 | 120 | 170 | sales |
| | Total Sales | 220 | 320 | |
| 3 | cost 1 | 80 | 100 | cogs |
| 4 | cost 2 | 90 | 110 | cogs |
| | Direct costs | 170 | 210 | |
| 5 | overhead 1 | 20 | 20 | ohead |
| 6 | overhead 2 | 22 | 22 | ohead |
| | Total OHead | 42 | 42 | |
| | Gross Profit | 50 | 110 | |
+----+--------------+--------+--------+-------+


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[johnwm]

Thank you very much. I should have looked more carefully at the local variables syntax.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller