SQL Query the average, maximum, and total number of rides users have taken-CodePudding

I need to create a derived table and calculate the avg, max, and total number of rides a user has taken using the travels table. The results should add the distinct USER_ID's to the average rides a user has take, the maximum rides a single user has taken, and the total rides all users have taken.

TRAVELS table

[Current Query]

SELECT
  COUNT(TRAVEL_ID) AS NUM,
  DISTINCT USER_ID  
FROM 
  (SELECT 
          AVG(NUM) AS Average,
          MAX(NUM) AS Maximum,
          NUM AS Total
   FROM TRAVELS) AS a;

[Expected Results]

Average 1.5714 | Maximum 2 | Total 11

CodePudding user response：

In the inner query count the number of travels for each user using group by, after that you can do the final aggregations.

select
    avg(cnt) as average,
    max(cnt) as maximum,
    sum(cnt) as total
from (
    select 
        USER_ID,
        count(TRAVEL_ID) as cnt
    from TRAVELS
    group by
        USER_ID
) as a