CODEWITHSUNDEEP
sqlamazon-redshiftunion
deek
deek

Reputation: 1095

SQL statement error when UNION is used with aggregates

I have this SQL I'm using with amazon redshift that errors out on the second select statement despite both queries being valid without a union:

select p.date,
       sum(p.pageviews) as "total_pageviews",
       sum(p.sessions)  as "total_sessions",
       l.location_id,
       p.brand_key
from public.local_site_sessions p, public.location_map l
where l.affiliate_id = p.affiliate_id
group by p.date, p.brand_key, l.location_id order by p.date

union

select g.date,
       g.sessions as "total_pageviews",
       g.pages_per_session as "total_sessions",
       g.location_id,
       brand_key
from public.ga_lead g
group by g.date, g.brand_key, g.location_id, g.sessions, g.pages_per_session    
order by g.date

The error says the second select statement is a syntax error? How can I correct this query?

Upvotes: 0

Views: 344

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You don't need group by for the second query .. you have not aggregated function 

select p.date, sum(p.pageviews) as "total_pageviews", sum(p.sessions) as "total_sessions",l.location_id, p.brand_key
FROM public.local_site_sessions p, public.location_map l
where l.affiliate_id = p.affiliate_id
group by p.date, p.brand_key, l.location_id 

union

select g.date, g.sessions , g.pages_per_session ,g.location_id, brand_key
FROM public.ga_lead g

And you should not use older implict join based on where use explicit join If you need order by apply the order by at the last select and you should not use alias for the second select 

select p.date, sum(p.pageviews) as "total_pageviews", sum(p.sessions) as "total_sessions",l.location_id, p.brand_key
FROM public.local_site_sessions p
INNER JOIN  public.location_map l ON  l.affiliate_id = p.affiliate_id
group by p.date, p.brand_key, l.location_id 

union

select g.date, g.sessions , g.pages_per_session ,g.location_id, brand_key
FROM public.ga_lead g
order by 1,4,5

Upvotes: 4

Himanshu
Himanshu

Reputation: 3970

Just add an outside select to your initial union query 

    Select * from
     ( Select.... union Select.... )

Upvotes: 0

Related Questions