sql - 试图只输出总额超过 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

我收到这个错误:

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.

Answer 1

你需要包含如下的 having 子句

    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