MySQL Forums
Forum List  »  Newbie

insert 0 when combing tables?
Posted by: m b
Date: September 24, 2010 03:41PM

i'm really new at this and have the following question. say I have this table:

CREATE TABLE `transactions` (
  `date` date DEFAULT NULL,
  `category` enum('gas','food') DEFAULT NULL,
  `cost` float(10,2) DEFAULT NULL

with these inserts:

INSERT INTO `transactions` (`date`,`category`,`cost`) VALUES ('2010-09-01','food','20.00');
INSERT INTO `transactions` (`date`,`category`,`cost`) VALUES ('2010-09-10','gas','10.00');
INSERT INTO `transactions` (`date`,`category`,`cost`) VALUES ('2010-09-15','food','40.00');
INSERT INTO `transactions` (`date`,`category`,`cost`) VALUES ('2010-08-20','food','35.00');

producing a table that would look like this:

date 		category	cost
2010-09-01	food		20.00
2010-09-10	gas		10.00
2010-09-15	food		40.00
2010-08-20	food		35.00

i'm able to query to produce these two tables:

item	august
food	35.00

item	septemper
gas	10.00
food	60.00

but I would like to query to combine them and include zeros where relevant. i.e. i would like to produce a table like this:

item	august	septemper
gas	0.00	10.00
food	35.00	60.00

any suggestions? thanks!

Options: ReplyQuote

Written By
insert 0 when combing tables?
m b
September 24, 2010 03:41PM
September 24, 2010 04:21PM
m b
September 26, 2010 10:11AM
September 26, 2010 12:43PM
m b
September 26, 2010 02:49PM

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.