Tutorial :Query returning same sums - oracle sql



Question:

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


Solution:1

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;  


Solution:2

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;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »