CODEWITHSUNDEEP
sqloracle-database
Amelia
Amelia

Reputation: 121

Inner Joining table on column issue

Below is my query, for some reason this keeps running and doesn't return anything. I wanted to inner join both tables and add condition booking_value=1 which happens to be in the table that I am joining. I want all the records to show up that has the same rental date as the second table but where booking _value= 1. 

select a.timestamp , SUM(DEMAND_TO_COME * BOARD_RATE) AS 
RevenueTotal, 
count(DEMAND_TO_COME * BOARD_RATE) as NumofTrans from(
select  PA_FCS.ob_location_id, 
PA_FCS.ib_location_id, 
PA_FCS.vehicle_class, 
PA_FCS.return_date,
PA_FCS.RENTAL_DATE,board_rate,
PA_FCS.Demand_to_come,
substr(PA_FCS.rental_date, 0,8) as timestamp
from PA_FCS 

inner join pa_reservation on pa_reservation.Rental_date = 
PA_FCS.rental_date where booking_value=1
) a
group by a.timestamp
order by a.timestamp;

This should work and give me three columns of the following

 Timestamp    RevenueTotal       NumofTrans
 20190220      51435.56745        123665

Not sure what I am doing wrong here.

Upvotes: 1

Views: 45

Answers (1)

vercelli
vercelli

Reputation: 4757

You could simplify your query like this (you don't need the subquery and some of the fields in it's projection):

SELECT
    substr(pa_fcs.rental_date, 0, 8) AS timestamp,
    SUM(pa_fcs.demand_to_come * board_rate) AS revenuetotal,
    COUNT(*) AS numoftrans
FROM
    pa_fcs
    INNER JOIN pa_reservation ON pa_reservation.rental_date = pa_fcs.rental_date
WHERE
    booking_value = 1
GROUP BY
    substr(pa_fcs.rental_date, 0, 8)
ORDER BY
    timestamp;

I'm not sure what you are trying to achieve with count(DEMAND_TO_COME * BOARD_RATE). If you want the number of records count(*) should do it.

Upvotes: 2

Related Questions