CODEWITHSUNDEEP
sqlgoogle-bigquery
Filipe Ferminiano
Filipe Ferminiano

Reputation: 8791

Unexpected keyword UNION in bigquery

I'm trying to run a UNION query like this:

#standardSQL
SELECT
    s.campaign_name, 
    date(event_date) as event_date,
    'email' medium,
    count(s.send_id) sends
    from pixel_logs.table1 s
    inner join pixel_logs.full_logs AS l on l.uid = s.uid
    where 1=1
    group by
    event_date,
    campaign_name
    order by
    event_date

UNION ALL

    select
    s.campaign_name,
    date(event_date) as event_date,
    'sms' medium,
    count(s.send_id)
    from pixel_logs.table2 s
    inner join pixel_logs.full_logs AS l on l.uid = s.uid
    where 1=1
    and account_id = 'xxxx'
    group by
    event_date,
    campaign_name
    order by
    event_date;

But I'm getting this message:

Syntax error: Unexpected keyword UNION at [21:1] Dismiss

How to fix this?

Upvotes: 1

Views: 4063

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

In UNION queries ORDER BY is typically applied once, at the very end, and not to each portion of the query. You can try removing ORDER BY from the first half of your query:

#standardSQL
select
    s.campaign_name, 
    date(event_date) as event_date,
    'email' medium,
    count(s.send_id) sends
from pixel_logs.table1 s
inner join pixel_logs.full_logs AS l on l.uid = s.uid
    where 1=1
group by
    event_date,
    campaign_name

UNION ALL

select
    s.campaign_name,
    date(event_date)
    'sms' medium,
    count(s.send_id)
from pixel_logs.table2 s
inner join pixel_logs.full_logs AS l on l.uid = s.uid
    where 1=1
    and account_id = 'xxxx'
group by
    event_date,
    campaign_name
order by
    event_date;

Also, you should not need any aliases in the second half of the UNION query, as the aliases assigned in the first half are what will be used.

Upvotes: 4

Related Questions