CODEWITHSUNDEEP
sqlgoogle-bigquery
Jeff Henry
Jeff Henry

Reputation: 19

Using UNION ALL to combine two queries into one table

Trying to combine two queries that find the average value of column 'duration_minutes' broken down into two criteria (column 'member_casual' - for which there are only 2 options 'member' or 'casual'. I have been trying a the following syntax, which does display the data that I want, but in two rows, rather than two columns:

SELECT * FROM(
    SELECT AVG(duration_minutes) as cas_avg
    FROM `case-study-319921.2020_2021_Trip_Data.2020_2021_Rides_Merged`
    WHERE member_casual = 'casual'
    UNION ALL
    SELECT AVG(duration_minutes) as mem_avg
    FROM `case-study-319921.2020_2021_Trip_Data.2020_2021_Rides_Merged`
    WHERE member_casual = 'member');

Resulting table:

Row cas_avg
1 40.81073227046788
2 11.345919528176575

How would I combine those to queries so that the result from row 2 would instead display as a column with the header "mem_avg" (the alias that was given in the query)?

Upvotes: 1

Views: 175

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

How would I combine those to queries so that the result from row 2 would instead display as a column with the header "mem_avg" (the alias that was given in the query)?

try below

SELECT 
  AVG(IF(member_casual = 'casual', duration_minutes, null) ) as cas_avg,
  AVG(IF(member_casual = 'member', duration_minutes, null) ) as mem_avg,
FROM `case-study-319921.2020_2021_Trip_Data.2020_2021_Rides_Merged`

with output

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You would use group by:

SELECT member_casual, AVG(duration_minutes) as cas_avg
FROM `case-study-319921.2020_2021_Trip_Data.2020_2021_Rides_Merged`
GROUP BY member_casual;

If there are more than two types, you may need to add:

member_casual in ('casual', 'member')

Upvotes: 0

Related Questions