Redshift: I try to use Union but it returns 3 columns instead of 4. What can I do?

Question

I have to find the streams that took place in a specific country and specific dates (overall_streams) and then for the same country and dates, I have to find the streams for a specific product.

In other words, I am trying to compare how the product did compared to the overall number of streams that took place in this place and time.

For this reason, I tried to use UNION (the subquery I did wouldn't give the right results).

Here is my- simplified- code:

Select age_group, gender, sum(streams) as product_streams
From t1 
Where product='A' 
And country= 'US'
And date= '1st week of July'
Group by 1,2

Union

Select age_group, gender, sum(streams) as overall_streams
From t1
Where country='US'
And date='1st week of July'
Group by 1,2

Notice the difference in the second query is that I haven't specified a product.

The results I get is 3 columns. The third column is named "product_streams" and it alternates between the product_streams and the overall_streams.

Example:

0-18  f   100
0-18  f   560
0-18  m   45
0-18  m   398

The results are correct, I just want to have 4 columns instead of 3. Like this:

age_group    gender    product_streams    overall_streams

Any ideas?

Yes, it was-- I had included them in my original query – Nel L Nov 26 '19 at 17:18 — Nel L, Nov 26 '19 at 17:18

Gordon Linoff · Accepted Answer · 2019-11-26T17:17:58.583

1

I think you want conditional aggregation:

Select age_group, gender,
       sum(streams) as overall_streams
       sum(case when product = 'A' then streams else 0 end) as product_streams
From t1 
Where country = 'US' and
      date = '1st week of July'
group by age_group, gender;

edited Nov 26 '19 at 17:17

answered Nov 26 '19 at 17:10

Gordon Linoff

1,242,037
58
646
786

Yeap, you are 100 right, worked perfectly. Thanks a lot :) – Nel L Nov 26 '19 at 17:15

Redshift: I try to use Union but it returns 3 columns instead of 4. What can I do?

1 Answers1