Hello,
I have Two Tables that hold the number of transactions a shop did per hour by date. They hold the type1trans which is the ones done outside the shop and type 2 done inside:
CREATE TABLE IF NOT EXISTS `type1trans` (
`date` date NOT NULL,
`hour` int(10) NOT NULL,
`shopcode` int(10) NOT NULL,
`trans` int(10) NOT NULL,
KEY `date` (`date`,`sitecode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `type2trans` (
`date` date NOT NULL,
`hour` int(10) NOT NULL,
`shopcode` int(10) NOT NULL,
`trans` int(10) NOT NULL,
KEY `date` (`date`,`sitecode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I want to do a query, (with a where shopcode=123 for just per shop) that will sum(trans) by date (not worried about by hour) where I can end up with the sum of type1 trans by date and sum of type 2 and also the sum of type 1 and type 2 (to get total) by date.
eg
type1trans | type2trans | totaltrans | date
100 200 300 2012-12-27
Its important to note that on some days the type1trans are 0 so they don't have a record for that date in the type1trans table.
thank you if you can help.
Open in new window