Solved

MYSQL Join two same tables

Posted on 2012-12-27
12
409 Views
Last Modified: 2013-01-01
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.
0
Comment
Question by:AUCKLANDIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 2
  • 2
12 Comments
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38723365
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38723392
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
 
LVL 1

Author Comment

by:AUCKLANDIT
ID: 38723403
thanks, I get this error #1248 - Every derived table must have its own alias
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 300 total points
ID: 38723406
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
 
LVL 1

Author Comment

by:AUCKLANDIT
ID: 38723537
#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
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 200 total points
ID: 38723658
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
 
LVL 1

Author Comment

by:AUCKLANDIT
ID: 38725651
Thanks  RehanYousaf but that does not work it gives this error #1241 - Operand should contain 1 column(s)
0
 
LVL 1

Author Comment

by:AUCKLANDIT
ID: 38725658
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
 
LVL 1

Author Comment

by:AUCKLANDIT
ID: 38725687
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
 
LVL 1

Author Comment

by:AUCKLANDIT
ID: 38725721
I changed the type1 and type2 table placing in query and it works thanks.
0
 
LVL 1

Assisted Solution

by:AUCKLANDIT
AUCKLANDIT earned 0 total points
ID: 38725727
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
 
LVL 1

Author Closing Comment

by:AUCKLANDIT
ID: 38734270
working
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question