CODEWITHSUNDEEP
sqloracle-database
joe
joe

Reputation:

Query returning same sums - oracle sql

Wonder if anyone can shed any light on a sql query I'm working with;

I have built this query;

SELECT  SUM(TICKET_TYPE.PRICE) AS TOTALCINEMASALES, CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE
FROM    RESERVATION, TICKET, TICKET_TYPE, CINEMA, PERFORMANCE
WHERE   TICKET_TYPE.TICKET_TYPE_ID = TICKET.TICKET_TYPE_ID
        AND TICKET.RESERVATION_ID = RESERVATION.RESERVATION_ID
        AND RESERVATION.PERFORMANCE_ID = PERFORMANCE.PERFORMANCE_ID
        AND CINEMA.LOCATION = 'SKIPTON'
        AND PERFORMANCE.PERFORMANCE_DATE BETWEEN to_date('01/03/2009','DD/MM/yyyy') AND to_date('07/04/2009','DD/MM/yyyy')
GROUP BY
       CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE
ORDER BY
       TOTALCINEMASALES;

Every time I run it, it returns the sam set of sums on each row for totalcinemasales, but I know there are different levels of tickets sales etc sat in the database, it does it for every loaction I change, any pointers as to how I could improve it ?

Thanks

Upvotes: 0

Views: 161

Answers (2)

Quassnoi
Quassnoi

Reputation: 425623

SELECT  SUM(TICKET_TYPE.PRICE) AS TOTALCINEMASALES, CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE
FROM    RESERVATION, TICKET, TICKET_TYPE, CINEMA, PERFORMANCE
WHERE   TICKET_TYPE.TICKET_TYPE_ID = TICKET.TICKET_TYPE_ID
        AND TICKET.RESERVATION_ID = RESERVATION.RESERVATION_ID
        AND RESERVATION.PERFORMANCE_ID = PERFORMANCE.PERFORMANCE_ID
        AND CINEMA.LOCATION = 'SKIPTON'
-- Added this
        AND PERFORMANCE.CINEMA_ID = CINEMA.CINEMA_ID
--
        AND PERFORMANCE.PERFORMANCE_DATE BETWEEN to_date('01/03/2009','DD/MM/yyyy') AND to_date('07/04/2009','DD/MM/yyyy')
GROUP BY
        CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE
ORDER BY
        TOTALCINEMASALES;

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

You forgot to join CINEMA, probably with PERFORMANCE.

Guess: 

AND CINEMA.CINEMA_ID = PERFORMANCE.CINEMA_ID

You could use the ANSI joins, when you get used to it, it is actually easier to read:

SELECT  SUM(TICKET_TYPE.PRICE) AS TOTALCINEMASALES, CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE
FROM    
   TICKET 
   inner join TICKET_TYPE
     on TICKET_TYPE.TICKET_TYPE_ID = TICKET.TICKET_TYPE_ID
   inner join RESERVATION
     on TICKET.RESERVATION_ID = RESERVATION.RESERVATION_ID
   inner join PERFORMANCE
     on RESERVATION.PERFORMANCE_ID = PERFORMANCE.PERFORMANCE_ID
   inner join CINEMA  /* the missing link */
     on CINEMA.CINEMA_ID = PERFORMANCE.CINEMA_ID
WHERE
   CINEMA.LOCATION = 'SKIPTON'
   AND PERFORMANCE.PERFORMANCE_DATE BETWEEN to_date('01/03/2009','DD/MM/yyyy') AND to_date('07/04/2009','DD/MM/yyyy')
GROUP BY
       CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE
ORDER BY
       TOTALCINEMASALES;

Upvotes: 2

Related Questions