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.
LVL 1
AUCKLANDITAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Pratima PharandeConnect With a Mentor Commented:
try this

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 ) Y on X.date = Y.date)
0
 
RehanYousafCommented:
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

0
 
Pratima PharandeCommented:
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)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Go Premium
 
AUCKLANDITAuthor Commented:
thanks, I get this error #1248 - Every derived table must have its own alias
0
 
AUCKLANDITAuthor Commented:
#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
0
 
RehanYousafConnect With a Mentor Commented:
SELECT
       ISNULL(t1.Type1Trans, 0) AS 'Type1Trans'
      ,ISNULL(t2.Type2Trans, 0) AS 'Type2Trans'
      ,ISNULL(t1.Type1Trans, 0) + ISNULL(t2.Type2Trans, 0) AS 'TotalTrans'
      ,t2.[DATE]
      ,t2.shopcode
FROM
      (
            SELECT
                   SUM(HOUR) AS 'Type2Hours'
                  ,SUM(trans) AS 'Type2Trans'
                  ,[DATE]
                  ,shopcode
            FROM
                  type2trans
            GROUP BY
                   shopcode
                  ,[DATE]
      )T2
      LEFT JOIN
      (
            SELECT
                   SUM(HOUR) AS 'Type1Hours'
                  ,SUM(trans) AS 'Type1Trans'
                  ,[DATE]
                  ,shopcode
            FROM
                  type1trans
            GROUP BY
                   shopcode      
                  ,[DATE]      
      )T1 ON t2.shopcode = t1.shopcode AND t2.DATE = t1.DATE
WHERE
      t2.shopcode = 231
0
 
AUCKLANDITAuthor Commented:
Thanks  RehanYousaf but that does not work it gives this error #1241 - Operand should contain 1 column(s)
0
 
AUCKLANDITAuthor Commented:
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 ) Y on X.date = Y.date

above looks to have worked (checking result) - I rmeoved the ) at the end.
0
 
AUCKLANDITAuthor Commented:
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?
0
 
AUCKLANDITAuthor Commented:
I changed the type1 and type2 table placing in query and it works thanks.
0
 
AUCKLANDITConnect With a Mentor Author Commented:
Select IFNULL(X.type1transsum ,0) as type1sum,IFNULL(Y.type2transsum,0) as type2sum , (IFNULL(X.type1transsum ,0) +IFNULL(Y.type2transsum,0)) , X.`date`, Y.shopcode
From (
Select sum(trans) as type1transsum
,`date` from type2trans where shopcode=1037
group by `date`
 )X
Left join (
Select sum(trans) as type2transsum
,`date`, shopcode from type1trans where shopcode=1037
group by `date` ) Y on X.`date` = Y.`date`
0
 
AUCKLANDITAuthor Commented:
working
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.