Tim Pilo February 2016

Case, Subquery, and Joins

What I'm trying to do is get avg tip_portion for all rides that are above average time, average time, and below average time. So return 3 lines. When I run this it says:

ERROR:  missing FROM-clause entry for table "trip_data"
LINE 11: on trip_data.trip_id= trip_data.trip_id

I've changed it to trip_data.trip_id= a.trip_id it says:

 ERROR:  column a.trip_id does not exist.

What am I doing wrong. Can I do this a simpler way?

select 
    AVG(tip / total_fare) as tip_portion, trip_time
from
    (
        select 
            avg(trip_time),
                case
                    when avg(trip_time) > 11.05 then 'Above Average'
                    when avg(trip_time) = 11.05 then 'Average'
                    when avg(trip_time) < 11.05 then 'Below Average'
                end as trip_time_group
        from
            trip_data
    ) as a
        join
    trip_fare ON trip_fare.trip_id = trip_data.trip_id
where
    pickup_date <> '2009-12-25'
group by trip_time;

Answers


drosam February 2016

You are not returning any trip_id column in your subquery. Also, you should put trip_data.trip_id= a.trip_id instead of trip_fare.trip_id= trip_data.trip_id as the trip_data does not exist in the main query.


Barmar February 2016

You need to move the CASE from the subquery into the main query.

SELECT AVG(tip/total_fare) AS tip_portion,
        CASE WHEN trip_time > avg_trip_time THEN 'Above Average'
             WHEN trip_time = avg_trip_time THEN 'Average'
             ELSE 'Below Average'
        END AS trip_time_group
FROM trip_fare
CROSS JOIN (SELECT AVG(trip_time) AS avg_trip_time
            FROM trip_data) AS a
GROUP BY trip_time_group

Post Status

Asked in February 2016
Viewed 1,364 times
Voted 5
Answered 2 times

Search




Leave an answer