This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MYSQL Join two same tables
Posted on 2012-12-27
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.
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.
[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
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
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)
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)
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)
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)
#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
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
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
Thanks RehanYousaf but that does not work it gives this error #1241 - Operand should contain 1 column(s)
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.
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.
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?
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?
Select IFNULL(X.type1transsum ,0) as type1sum,IFNULL(Y.type2tra
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`
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`
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar.
Int…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 14 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.