BigQuery how to calculate average DATETIME

I just can't figure out how to get the average of travel time ended_at and started_at using DATETIME_DIFF. So there are two possible values in column member_casual and I want to figure out how to group the average travel times per member group. ie. return two rows with one value on each row, the average travel time for the group. I've tried searching but I've failed to translate the solutions to my issue.

SELECT 
  member_casual,
  DATETIME_DIFF(started_at, ended_at, MINUTE)  
  CASE WHEN ended_at < started_at THEN 24 ELSE 0 end
FROM dataset
GROUP BY
  member_casual,
  started_at,
  ended_at
LIMIT 100

I've tried adding AVG(...) in several places but I guess I just don't know enough about SQL yet to figure this out.

CASE is used to fix the error that happens when the travel period passes midnight.

CodePudding user response：

Basically you only add an average aroud the date_diff and the query will get the aveage for every member_casual

also a LIMIT with Out order By makes no sense

SELECT 
  member_casual,
  AVG(DATETIME_DIFF(started_at, ended_at, MINUTE)  
  CASE WHEN ended_at < started_at THEN 24 ELSE 0 end) avd_date_diff
FROM dataset
GROUP BY
  member_casual
ORDER BY member_casual
LIMIT 100