CODEWITHSUNDEEP
oracle-database
David Garcia
David Garcia

Reputation: 2696

Select greater than ORACLE

I've tried different variations of the following statement, the result is half of what it must do, I dont know what's missing.

SELECT DISTINCT COUNT(*) AS NO_RESERVATIONS, HG.RESV_ID, HR.BOOKING_CUS_ID AS BOOKED_BY, C.CUS_NAME
FROM HOLIDAY_RESERVATION HR INNER JOIN(HOLIDAY_GROUP HG INNER JOIN CUSTOMER C
ON HG.CUS_ID = C.CUS_ID)
ON HR.BOOKING_CUS_ID = HG.CUS_ID
WHERE HR.RESV_ID >= 2
GROUP BY HG.RESV_ID, HR.BOOKING_CUS_ID, C.CUS_NAME;

Yields

NO_RESERVATIONS    RESV_ID  BOOKED_BY CUS_NAME
--------------- ---------- ---------- --------------------
              1          3          5 Beatrice P. Rosa
              1          8         15 Phillip B. Fleming
              1          7         13 Debra V. Key
              1          4          7 Magee A. Pace
              2         11          3 Hadassah T. Hebert
              1          5          9 Portia D. Melton
              2          2          3 Hadassah T. Hebert
              1          6         11 Larissa X. Boyer
              1          9         17 Wayne F. Burnett
              1         10         19 Eleanor J. Padilla

10 rows selected.

It should only show the person who has made two or more reservations, in this case Hadassah T. Hebert

ANSWER Thanks Brian for the help

SELECT DISTINCT C.CUS_NAME AS BOOKED_BY, COUNT(*) AS NO_RESERVATIONS
FROM HOLIDAY_RESERVATION HR INNER JOIN(HOLIDAY_GROUP HG INNER JOIN CUSTOMER C
ON HG.CUS_ID = C.CUS_ID)
ON HR.BOOKING_CUS_ID = HG.CUS_ID
GROUP BY HG.RESV_ID, HR.BOOKING_CUS_ID, C.CUS_NAME
HAVING count(*) > 1;

Yields

BOOKED_BY            NO_RESERVATIONS
-------------------- ---------------
Hadassah T. Hebert                 2

Upvotes: 0

Views: 6946

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

To filter out grouped results, you need to use the HAVING statement. So for your example, your query would look like:

SELECT DISTINCT COUNT(*) AS NO_RESERVATIONS, HG.RESV_ID, HR.BOOKING_CUS_ID AS BOOKED_BY, C.CUS_NAME
FROM HOLIDAY_RESERVATION HR INNER JOIN(HOLIDAY_GROUP HG INNER JOIN CUSTOMER C
ON HG.CUS_ID = C.CUS_ID)
ON HR.BOOKING_CUS_ID = HG.CUS_ID
WHERE HR.RESV_ID >= 2
GROUP BY HG.RESV_ID, HR.BOOKING_CUS_ID, C.CUS_NAME
HAVING count(*) > 1;

Upvotes: 5

Related Questions