SQL not using ALIAS column for calculation

Question Statement – From the given trips and users tables for a taxi service, write a query to return the cancellation rate in the first two days in October, rounded to two decimal places, for trips not involving banned riders or drivers.

Question code on Oracle SQL.

create table trips (trip_id int, rider_id int, driver_id int, status varchar2(200), request_date date);
insert into trips values (1, 1, 10, 'completed', to_date ('2020-10-01', 'YYYY/MM/DD'));
insert into trips values (2, 2, 11, 'cancelled_by_driver', to_date ('2020-10-01', 'YYYY/MM/DD'));
insert into trips values (3, 3, 12, 'completed', to_date ('2020-10-01', 'YYYY/MM/DD'));
insert into trips values (4, 4, 10, 'cancelled_by_driver', to_date ('2020-10-02', 'YYYY/MM/DD'));
insert into trips values (5, 1, 11, 'completed', to_date ('2020-10-02', 'YYYY/MM/DD'));
insert into trips values (6, 2, 12, 'completed', to_date ('2020-10-02', 'YYYY/MM/DD'));
insert into trips values (7, 3, 11, 'completed', to_date ('2020-10-03', 'YYYY/MM/DD'));

create table users (user_id int, banned varchar2(200), type varchar2(200));
insert into users values (1, 'no', 'rider');
insert into users values (2, 'yes', 'rider');
insert into users values (3, 'no', 'rider');
insert into users values (4, 'no', 'rider');
insert into users values (10, 'no', 'driver');
insert into users values (11, 'no', 'driver');
insert into users values (12, 'no', 'driver');

My Solution Code is below. However, I get the following error. Can someone pleas help?

ORA-00904: "TOTAL_TRIPS": invalid identifier

SOLUTION CODE:

select request_date, (1-(trips_completed/total_trips)) as "cancel_rate"
from
((
select request_date,
sum(case when status = 'completed' then 1 else 0 end) as "trips_completed",
sum(case when status = 'cancelled_by_driver' then 1 else 0 end) as "trips_cancelled",
sum(case when status = 'cancelled_by_driver' then 1 when status= 'completed' then 1 else 0 end) as "total_trips"
from 
(
select t.rider_id, t.driver_id, t.status, t.request_date, u.banned as "not_banned_rider", u.banned as "not_banned_driver"
from trips t
join users u
on t.rider_id=u.user_id
where u.banned='no'
)
group by request_date
having request_date <> to_date ('2020-10-03', 'YYYY/MM/DD')
));

Answer

ORA-00904: “TOTAL_TRIPS”: invalid identifier

just means what is written “total_trips” is invalid

Just use total_trips (without quote)

Advertisement

Answer