troubleshooting Question

MYSQL Join two same tables

Avatar of AUCKLANDIT
AUCKLANDITFlag for New Zealand asked on
MySQL ServerSQL
12 Comments3 Solutions449 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros