SQL Query help

[yvetteness]

hiya im doing a database and im required to list all customers who have made two reservations or more. we have tried the following, and many other, but still cant figure it out.

SELECT BO_ID, COUNT(C_ID) AS NUMBER_OF_RESERVATIONS
FROM BOOKING
WHERE
C_ID < 2;


SELECT BO_ID, COUNT(C_ID) AS NUMBER_OF_RESERVATIONS, C_NAME
FROM BOOKING, CUSTOMER
WHERE
BOOKING.C_ID = CUSTOMER.C_ID
AND CUSTOMER.C_ID = CUSTOMER.C_NAME
AND
ORDER NUMBER_OF_RESERVATIONS >= 2;

SELECT C_ID AS NUMBER_OF_RESERVATIONS, C_NAME, BO_ID
FROM BOOKING
WHERE C_ID > 2;

could anybody shed some light please?

[ludwig]

try this

select bo_id, count(*) from booking
group by bo_id
having count(*) > 2

[ludwig]

..sorry, that should be >= 2

[MarkRae]

As Ludwig has correctly answered, you can't use WHERE to filter results on an aggregated expression, and must use HAVING instead:

http://msdn.microsoft...library/ms173260.aspx

[beso]

My guess is this is what you are looking for:

SELECT COUNT(BO_ID) AS NUMBER_OF_RESERVATIONS, CUSTOMER.C_ID, C_NAME
FROM BOOKING INNER JOIN CUSTOMER
ON BOOKING.C_ID = CUSTOMER.C_ID
GROUP BY CUSTOMER.C_ID, C_NAME
HAVING COUNT(BO_ID) >= 2
ORDER BY COUNT(C_ID), C_NAME;