ansaurus

Question

Answer 1

A:

You need exclude "ticket.ticket_id" from group clause (this unique value making unnecessary groups - result strings):

select count(ticket.ticket_id) as tickets_sold, "PERFORMANCE"."PERFORMANCE_DATE" as "PERFORMANCE_DATE",
         "FILM"."FILM_TITLE" as "FILM_TITLE",
         "TICKET"."TICKET_ID" as "TICKET_ID",
         "CINEMA"."LOCATION" as "LOCATION",
         "PERFORMANCE"."PERFORMANCE_TIME" as "PERFORMANCE_TIME" 
     from    "TICKET" "TICKET",
         "RESERVATION" "RESERVATION",
         "PERFORMANCE" "PERFORMANCE",
         "RUN" "RUN",
         "SCREEN" "SCREEN",
         "CINEMA" "CINEMA",
         "FILM" "FILM" 
     where   "PERFORMANCE"."PERFORMANCE_ID"="RESERVATION"."PERFORMANCE_ID"
     and     "PERFORMANCE"."RUN_ID"="RUN"."RUN_ID"
     and     "RUN"."FILM_ID"="FILM"."FILM_ID"
     and     "RUN"."SCREEN_ID"="SCREEN"."SCREEN_ID"
     and     "SCREEN"."CINEMA_ID"="CINEMA"."CINEMA_ID"
     and     "TICKET"."RESERVATION_ID"="RESERVATION"."RESERVATION_ID"
      and    "PERFORMANCE"."PERFORMANCE_DATE" ='1-mar-09' 
       and   "FILM"."FILM_TITLE" ='PIRATES OF THE CARIBBEAN - AT WORLDS END' 
       and   "CINEMA"."LOCATION" ='bradfordeast' 
       and   "PERFORMANCE"."PERFORMANCE_TIME" ='20:00'
group by reservation.performance_id, performance.performance_date,
film.film_title, ticket.ticket_id,cinema.location, performance.performance_time
order by tickets_sold DESC;

drnk 2009-04-19 22:07:09

and from "select" clause too!

drnk 2009-04-19 22:07:52

hi - thanks for the comment, i tryed something similar earlier - just tried again - along the lines of what you said and the same error came up - ORA-00979: not a GROUP BY expression. this doesn't show when the tick.ticket_id is n the group by - thanks

2009-04-19 22:18:29

you need exclude "ticket.ticket_id" from "group by" clause, and from "select" clause too ("TICKET"."TICKET_ID" as "TICKET_ID"). and thats will be good. unfortunately, i can`t edit my answer ( and i wrote bad comment

drnk 2009-04-19 22:39:10

all looks good - I understand why it was not working now - thanks! not sure how to mark this as answered - or if i need to ? - first time on here.

2009-04-19 23:01:45

Answer 2

+1 A:

It doesn't make sense to include both ticket_id and COUNT(ticket_id) when you GROUP BY ticket_id.

I infer that your schema has relationships something like the following:

TICKETS --> RESERVATION --> PERFORMANCE --> RUN --> SCREEN --> CINEMA
                                   FILM --> RUN

It looks like your desired report could be described as the following:

Count tickets per film, performance date & time, and cinema location.

It's important in any query using GROUP BY that the columns in your SELECT-list match the columns in your GROUP BY list (except for columns inside aggregate functions such as COUNT()). This is called the Single-Value Rule, and it's enforced in standard SQL. These would be the columns following "per" in the report description.

Here's how I'd write the query:

SELECT COUNT(t."TICKET_ID") AS tickets_sold, 
 f."FILM_TITLE",
 c."LOCATION",
 p."PERFORMANCE_DATE",
 p."PERFORMANCE_TIME",
FROM "TICKET" t,
 JOIN "RESERVATION" r ON (r."RESERVATION_ID" = t."RESERVATION_ID") 
 JOIN "PERFORMANCE" p ON (p."PERFORMANCE_ID" = r."PERFORMANCE_ID")
 JOIN "RUN" run ON (run."RUN_ID" = p."RUN_ID")
 JOIN "SCREEN" s ON (s."SCREEN_ID" = run."SCREEN_ID")
 JOIN "CINEMA" c ON (c."CINEMA_ID" = s."CINEMA_ID")
 JOIN "FILM" f ON (f."FILM_ID" = run."FILM_ID")
WHERE p."PERFORMANCE_DATE" ='1-mar-09'
 AND f."FILM_TITLE" ='PIRATES OF THE CARIBBEAN - AT WORLDS END' 
 AND c."LOCATION" ='bradfordeast' 
 AND p."PERFORMANCE_TIME" ='20:00'
GROUP BY f."FILM_TITLE", c."LOCATION", p."PERFORMANCE_DATE", p."PERFORMANCE_TIME" 
ORDER BY tickets_sold DESC;

Note that I used standard JOIN syntax, moving the join conditions into ON clauses. The WHERE clause includes only query restrictions. Logically, there's no difference in the result of the query, but for the sake of clarity, I prefer to keep join conditions close to the respective tables in the FROM clause.

I also used single-letter table aliases to keep things more readable (except r versus run because they both start with r).

Bill Karwin 2009-04-19 23:08:20

that looks great - I have had a look at this, however I keep getting oro00936, missing expression - I have studied the script and it all looks good to me ?

2009-04-20 19:52:06

ansaurus

tags:

views:

answers:

Oracle count statement problem

related questions