Question 21484 in Mysql Question posted in
The official MySQL documentation can be found here.

SELECT without FROM clause cannot use aggregation – Mysql

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

started_at ended_at member_casual
2022-01-14 08:55:03 UTC 2022-01-14 09:01:11 UTC member

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search