Trying to output only reservations totaling over $2000

Question

SELECT 
    reservationWH.RNUM AS Reservation, 
    packageWH.PNAME AS "Package Name", 
    (COUNT(guestWH.CELL_PHONE) * packageWH.COSTPERSON) AS "Total Cost"
FROM 
    packageWH 
INNER JOIN 
    reservationWH ON packageWH.P_ID = reservationWH.P_ID 
INNER JOIN 
    guestWH ON reservationWH.RNUM = guestWH.RNUM
WHERE 
    (COUNT(guestWH.CELL_PHONE)) * packageWH.COSTPERSON > 2000
GROUP BY 
    reservationWH.RNUM
ORDER BY 
    reservationWH.RNUM ASC

I get this error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Use sub-queries to store the result of group by clause and then do the required join and the required where — Sahil Dhoked, Dec 05 '18 at 05:51

Fahmi · Answer 1 · 2018-12-05T05:28:06.717

You need include having clause like below

    SELECT reservationWH.RNUM AS Reservation, packageWH.PNAME AS "Package Name" , 
   COUNT(guestWH.CELL_PHONE)* sum(packageWH.COSTPERSON) AS "Total Cost"
    FROM packageWH INNER JOIN reservationWH ON packageWH.P_ID=reservationWH.P_ID INNER JOIN guestWH ON reservationWH.RNUM=guestWH.RNUM
    GROUP BY reservationWH.RNUM,packageWH.PNAME
    having (COUNT(guestWH.CELL_PHONE)*sum(packageWH.COSTPERSON)) > 2000
    ORDER BY reservationWH.RNUM ASC

score 0 · Answer 2 · answered Dec 05 '18 at 05:45

With no access to data, nor any visibility of the wanted result, solving this is something of a guessing game. Perhaps you need to move the grouping into a subquery, and this way you can use a where clause, e.g.

SELECT
    reservationWH.RNUM                   AS Reservation
  , packageWH.PNAME                      AS "Package Name"
  , (g.num_cells * packageWH.COSTPERSON) AS "Total Cost"
FROM packageWH
INNER JOIN reservationWH ON packageWH.P_ID = reservationWH.P_ID
INNER JOIN (
        SELECT
            RNUM
          , COUNT( CELL_PHONE ) num_cells
        FROM guestWH
        GROUP BY
            RNUM
    ) g ON reservationWH.RNUM = g.RNUM
WHERE (g.num_cells * packageWH.COSTPERSON) > 2000
ORDER BY
    reservationWH.RNUM ASC

But, without sample data and expected result this is impossible for me to verify.

score 0 · Answer 3 · answered Dec 05 '18 at 13:07

If I assume that you want one row per reservation, then remove PNAME from the query. If I further assume that g.CELL_PHONE is never NULL, then:

SELECT r.RNUM AS Reservation, 
       SUM(p.COSTPERSON) AS "Total Cost"
FROM packageWH p INNER JOIN 
     reservationWH
     ON p.P_ID = r.P_ID INNER JOIN 
     guestWH g
     ON r.RNUM = g.RNUM
GROUP BY r.RNUM
HAVING SUM(p.COSTPERSON) > 2000
ORDER BY r.RNUM ASC;

Notes:

You want the filtering on the aggregation column in the HAVING clause not the WHERE clause.
If you want one row per reservation, then you should be aggregating only by the aggregation.
You seem to want to sum the costs per person, so SUM() makes more sense than COUNT().
Table aliases make the query easier to write and to read.

score 0 · Answer 4 · answered Feb 14 '20 at 05:55

For the benefit of users googling error messages:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

An aggregate is a grouping function such as COUNT, SUM, MAX, MIN, ...

You can find the full list here: https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql

In other words, you cannot have a grouping function in your where section unless you use it in a sub-query.

WHERE x = (SELECT SUM(y) FORM z)

In the context of this question, you probably just need to use a having clause instead.

SELECT 
    reservationWH.RNUM AS Reservation, 
    packageWH.PNAME AS "Package Name", 
    (COUNT(guestWH.CELL_PHONE) * packageWH.COSTPERSON) AS "Total Cost"
FROM 
    packageWH 
INNER JOIN 
    reservationWH ON packageWH.P_ID = reservationWH.P_ID 
INNER JOIN 
    guestWH ON reservationWH.RNUM = guestWH.RNUM
GROUP BY 
    reservationWH.RNUM
HAVING
    (COUNT(guestWH.CELL_PHONE)) * packageWH.COSTPERSON > 2000
ORDER BY 
    reservationWH.RNUM ASC

Trying to output only reservations totaling over $2000

4 Answers4