Reputation: 199
I dont get it. I try to sum up two identical tables with union but it doesnt works.
t_fb_data:
id | date | sum
---------------------
1 | 2017.01.01| 2
2 | 2017.01.02| 1
t_google_data:
id | date | sum
---------------------
1 | 2017.01.01| 7
2 | 2017.01.02| 1
3 | 2017.01.03| 2
result could looks like:
| date | sum
------------------
| 2017.01.01| 2
| 2017.01.02| 1
| 2017.01.01| 7
| 2017.01.02| 1
| 2017.01.03| 2
This is my Query:
SELECT *
FROM
( SELECT date
, sum
FROM t_fb_data
) T1
Union
( SELECT date
, sum
FROM t_google_data
) T2
T2 should be wrong because my workbench want an parenthesis in front of T2. But if i do this its wrong two. Do i have to setup something on my workbench?
Upvotes: 2
Views: 524
Reputation: 18577
You can use UNION ALL for sure, but if you want to stick to UNION only then try this,
SELECT *
FROM
( SELECT date
, sum, 'fb' identifier
FROM t_fb_data
) T1
Union
( SELECT date
, sum, 'google' identifier
FROM t_google_data
) T2
This is because, your data from two queries will give identical records which will be merged into single record, so if you will write identifer both tables will have differential records.
I hope this will help.
EDIT
( SELECT date
, sum, 'fb' identifier
FROM t_fb_data
)
Union
( SELECT date
, sum, 'google' identifier
FROM t_google_data
)
Upvotes: 0
Reputation: 39537
Simply do:
SELECT
date, sum
FROM
t_fb_data
UNION SELECT
date, sum
FROM
t_google_data
or put it in a subquery:
SELECT
*
FROM
(SELECT
date, sum
FROM
t_fb_data UNION SELECT
date, sum
FROM
t_google_data) t;
Upvotes: 1
Reputation: 13
In MySQL, if you want to sum up identical values from different data sets, you need to use the keyword ALL, because the union alone will sum up the data sets in an unique data sets and apply a DISTINCT on it. Your query should look like this:
SELECT date,sum FROM t_fb_data Union ALL SELECT date,sum FROM t_google_data
Upvotes: 0
Reputation: 317
Your query is wrong. You try to union two subqueries having SELECT
statement at top refering to first subquery.
Code that You are looking for:
SELECT date, sum FROM t_fb_data
UNION
SELECT date, sum FROM t_google_data
That will return non duplicated values from both tables.
If you want to use this as a subquery, you can simply surround that with another SELECT
statement as on example below:
SELECT sub.*
FROM (
SELECT date, sum FROM t_fb_data
UNION
SELECT date, sum FROM t_google_data
) sub
In this case, You can do additonal calculations in your top SELECT
statement, if needed.
Upvotes: 1