asked on

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.

RehanYousaf

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

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

ASKER

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

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AUCKLANDIT

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AUCKLANDIT

ASKER

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

AUCKLANDIT

ASKER

AUCKLANDIT

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

ASKER

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

SOLUTION

AUCKLANDIT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AUCKLANDIT

ASKER

working