Re: [student] BQFH

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Mon, 26 Oct 1998 10:02:29 -0800
Message-ID: <3634B935.616B54FE@uclink4.berkeley.edu>

Oliver,

     MAX and SUM functions don't nest very well. If you want to return the film_name and subtot for the film with the MAX(subtot), one solution, although relatively ugly, would be:

SELECT F.FILM_NAME,

       SUM(S2.CONC_SEATS_SOLD)
       + SUM(S2.ORD_SEATS_SOLD) GRAND_SUB
  FROM (SELECT MAX(SUBTOT) new_max
          FROM (  SELECT SUM(S.CONC_SEATS_SOLD)
                         + SUM(S.ORD_SEATS_SOLD) SUBTOT
                    FROM SCREENING S
                GROUP BY S.FILM_NO)) max_film,
       film f, screening s2

 WHERE s2.film_no = f.film_no
GROUP BY f.film_name, max_film.new_max
HAVING max_film.new_max = SUM(S2.CONC_SEATS_SOLD)

                          + SUM(S2.ORD_SEATS_SOLD)

...this query will also pull out multiple records if more than one film's SUBTOT is maximum. You could also join the table to itself and get the top-n rows where n = 1, but that would be pretty slow on big tables.

Jay!!!

Oliver White wrote:

> I have been given much grief by this query, obviously the syntax is
> completely wrong, but I have tried putting the MAX function in all
> sorts of places. In this case I get a "SQL command not properly ended"
> error, but in other configurations the concentric grouping functions
> are not allowed. How would I find the maximum of the subtotals, what
> would be the syntax?
>
> SELECT F.FILM_NAME, SUM(S.CONC_SEATS_SOLD) + SUM(S.ORD_SEATS_SOLD)
> SUBTOT
> FROM FILM F, SCREENING S
> WHERE S.FILM_NO = F.FILM_NO
> ORDER BY S.FILM_NO
> HAVING SUBTOT = MAX(SUBTOT);
> --
> On the smeeter.
> MHM 22x9
> --
Received on Mon Oct 26 1998 - 12:02:29 CST