1407. Top Travellers ending in runtime error

raikumardipak 0
Viewed 14

I attempted the 1407. Top Travellers. But am struggling with my Oracle query below, 'Runtime error'. A little too tired to understand why. Any idea where I am going wrong? Have been rusty with SQL of late. :(

select name as name, 
case when rides.distance is null then 0 else sum(rides.distance) end as travelled_distance
from users
left join rides
on users.id = rides.user_id
group by rides.users_id
order by travelled_distance desc, name;
oracle sql
1 Answers

As commented, is another way round:

select 
  name,
  sum(case when rides.distance is null then 0 else rides.distance end) as travelled_distance
from users left join rides on users.id = rides.user_id
group by name
order by travelled_distance desc, name;

Or, simpler, use the nvl function:

select 
  name,
  sum(nvl(rides.distance, 0)) as travelled_distance
from ...

Though, a few more objections:

  • you should use table aliases (as they simplify query and improve readability)
    • moreover, you should precede all column names with table aliases; in your case, you failed to do so for the name column. It probably belongs to the users table, but we can't tell for sure as we don't have your data model nor access to your database
  • group by clause should contain column(s) that aren't aggregated. In your query, that's the name column. You can put rides.users_id into that clause, but you must put name in there
Littlefoot0
Related