AUCKLANDIT

asked on 12/27/2012

MYSQL Join two same tables

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.

MySQL ServerSQL

Last Comment

AUCKLANDIT

8/22/2022 - Mon

RehanYousaf

12/27/2012

I have done the query in MS SQL but you can easily modify it for MySQL

DECLARE @type1trans TABLE (
  [DATE] DATE NOT NULL,
  [HOUR] INT NOT NULL,
  shopcode INT NOT NULL,
  trans INT NOT NULL
) 

INSERT INTO @type1trans VALUES ('2012-12-01', 4, 231, 17)
INSERT INTO @type1trans VALUES ('2012-12-01', 5, 321, 15)
INSERT INTO @type1trans VALUES ('2012-12-03', 3, 231, 12)
INSERT INTO @type1trans VALUES ('2012-12-04', 6, 231, 10)

DECLARE @type2trans TABLE (
  [DATE] DATE NOT NULL,
  [HOUR] INT NOT NULL,
  shopcode INT NOT NULL,
  trans INT NOT NULL
)

INSERT INTO @type2trans VALUES ('2012-12-01', 4, 321, 45)
INSERT INTO @type2trans VALUES ('2012-12-02', 4, 321, 45)
INSERT INTO @type2trans VALUES ('2012-12-01', 6, 231, 14)
INSERT INTO @type2trans VALUES ('2012-12-02', 6, 231, 10)
INSERT INTO @type2trans VALUES ('2012-12-03', 6, 231, 24)
INSERT INTO @type2trans VALUES ('2012-12-04', 6, 231, 42)
INSERT INTO @type2trans VALUES ('2012-12-05', 6, 231, 5)
INSERT INTO @type2trans VALUES ('2012-12-03', 8, 543, 40)


;WITH cteType2 AS (
	SELECT 
		 SUM(HOUR) AS 'Type2Hours'
		,SUM(trans) AS 'Type2Trans'
		,[DATE]
		,shopcode
	FROM
		@type2trans
	GROUP BY
		 shopcode
		,[DATE]
)
,cteType1 AS (
	SELECT 
		 SUM(HOUR) AS 'Type1Hours'
		,SUM(trans) AS 'Type1Trans'
		,[DATE]
		,shopcode
	FROM
		@type1trans
	GROUP BY
		 shopcode	
		,[DATE]	
)
SELECT 
	 ISNULL(c1.Type1Trans, 0) AS 'Type1Trans'
	,ISNULL(c2.Type2Trans, 0) AS 'Type2Trans'
	,ISNULL(c1.Type1Trans, 0) + ISNULL(c2.Type2Trans, 0) AS 'TotalTrans'
	,c2.[DATE]
	,c2.shopcode
FROM 
	cteType2 c2
	LEFT JOIN cteType1 c1 ON c2.shopcode = c1.shopcode AND c2.DATE = c1.DATE
WHERE
	c2.shopcode = 231

Open in new window

Pratima

12/27/2012

Select X.type1transsum ,Y.type2transsum , (X.type1transsum +Y.type2transsum) , X.date
From (
Select sum(trans) as type1transsum
,date from type1trans
group by date
)X
Left join (
Select sum(trans) as type2transsum
,date from type2trans
group by date ) on X.date = Y.date)

AUCKLANDIT

12/27/2012

ASKER

thanks, I get this error #1248 - Every derived table must have its own alias

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

ASKER CERTIFIED SOLUTION

Pratima

12/27/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

AUCKLANDIT

12/27/2012

ASKER

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 10

SOLUTION

RehanYousaf

12/27/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

AUCKLANDIT

12/27/2012

ASKER

Thanks RehanYousaf but that does not work it gives this error #1241 - Operand should contain 1 column(s)

AUCKLANDIT

12/27/2012

ASKER

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

AUCKLANDIT

12/27/2012

ASKER

this works to show what I want but:

Select X.type1transsum ,Y.type2transsum , (X.type1transsum +Y.type2transsum) , X.`date`
From (
Select sum(trans) as type1transsum
,`date` from type1trans where shopcode=1037
group by `date`
)X
Left join (
Select sum(trans) as type2transsum
,`date` from type2trans where shopcode=1037
group by `date` ) Y on X.`date` = Y.`date`

For the dates where there is no records in the type1trans then there is no record shown for the type2 dates and a total for that date being the same as type2 since type1 null. How can I get type1 to return null for the date?

AUCKLANDIT

12/27/2012

ASKER

I changed the type1 and type2 table placing in query and it works thanks.

SOLUTION

AUCKLANDIT

12/27/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial