oracle - sum on a subquery ?

Question

Hi,

Wonder if anyone can help me understand how to sum up the column of single column, ie a list of costs into one total cost.

I have been looking into this, and I think I'm on the right lines in undertsanding i need to sum the query, treat it as a subquery. However I'm not having much luck - do I need to give the subquery an alias, or is it a straight case of wrapping the query in a sum?

Here is the working query I want to sum up, all my attempts at sum left out for clarity!

SELECT TICKET_TYPE.PRICE AS TOTALSALES, RESERVATION.RESERVATION_ID,
       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 = 'sometown'
      AND PERFORMANCE.PERFORMANCE_DATE = to_date('01/03/2009','DD/MM/yyyy');

some of the data...

TOTALSALES RESERVATION_ID LOCATION PERFORMANCE_DATE
    2.8     1     sometown     01-MAR-09
    3.5     2     sometown     01-MAR-09
    2.8     3     sometown     01-MAR-09
    2.8     3     sometown     01-MAR-09
    2.8     3     sometown     01-MAR-09
    2       4     sometown     01-MAR-09
    2.8     5     sometown     01-MAR-09

Thanks !

Answer 1

You can sum a single column with

select sum(mycolumn) from mytable

When you mix aggregators (e.g., sum(), count()) in the select list with fields then you change the meaning of the query. You have to include a GROUP BY clause and the clause must contain every non-aggregate part of the select list.

You could do the sum, by itself, in a nested subquery then include that output in the outer select...

Answer 2

Try:

You need to include a group by if you want the totals per ID.

SELECT SUM(TICKET_TYPE.PRICE) AS TOTALSALES
          , RESERVATION.RESERVATION_ID
          , 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 = 'sometown' 
        AND PERFORMANCE.PERFORMANCE_DATE = to_date('01/03/2009','DD/MM/yyyy');
     GROUP BY RESERVATION.RESERVATION_ID

** pretty much the same answer as above, I need to get better at refreshing before posting**

Answer 3

What you first need to know is what the "non summed part" of the query should be.

You want to calculate a SUM of X by Y (Sum of SALES by MONTH for example). Y can be any number of fields and the resulting data set will contain one record for each distinct combination of the Y fields.

Once you know that we can help you write your query.