nicoschuck
nicoschuck

Reputation: 199

MySQL union missing parenthesis

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

Answers (4)

Rahul
Rahul

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

Gurwinder Singh
Gurwinder Singh

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

Mhalgan
Mhalgan

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

drstonecodez
drstonecodez

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

Related Questions