I am trying to get the average ride length duration for casual and member riders from the dataset "all_year" and list it as average_trip_duration_all_year Code I am trying to run:

SELECT member_casual, CASE
WHEN member_casual = 'member' THEN (SELECT AVG(ended_at - started_at))
WHEN member_casual = 'casual' THEN (SELECT AVG(ended_at - started_at))
END AS average_trip_duration_all_year
FROM `savvy-night-365318.Cyclist.all_year`
GROUP BY member_casual;

example of dataset

Not sure what I am doing wrong, any help would be greatly appreciated!

The expected outcome would be to see the average_trip_duration_all_year for casual and member riders for the whole year.

CodePudding user response：

It looks like you are trying to do the same thing in both branches of the CASE statement. I think you should be able to select the average directly, like this:

SELECT member_casual, AVG(ended_at - started_at) AS average_trip_duration_all_year
FROM `savvy-night-365318.Cyclist.all_year`
GROUP BY member_casual;

CodePudding user response：

You're doing the same thing in both branches of the case. Maybe that's a typo, first let's assume it's not.

Since you're already grouping by member_casual there's no need for the case. If you only want to group when member_casual is 'member' or 'casual' use a where clause.

select
  member_casual,
  avg(ended_at - started_at) as average_trip_duration_all_year
from `savvy-night-365318.Cyclist.all_year`
where member_casual in ('member', 'casual')
group by member_casual

Let's assume it is a typo and you want to do the math differently for each. Make the case the expression you pass to avg.

select 
  member_casual,
  avg(
    case
    when member_casual = 'member' then ended_at - started_at
    when member_casual = 'casual' then now() - started_at
    end
  ) as average_trip_duration_all_year
from `savvy-night-365318.Cyclist.all_year`
where member_casual in ('member', 'casual')
group by member_casual;

Demonstration.