Oracle SQL - not a GROUP BY function

Question

I am trying a very simple query to select an ID number, name, 2 dates, the days in between those dates, and the sum of several costs from a view.

However no matter how I tinker with it all I get is this error.

I know I'm not being overly helpful, but I honestly don't know whats going wrong (I'm kinda really new to this). Thanks in advance, code is below.

SELECT vw.RESERVATION_NUMBER AS "Reservation Number"
        , owner.OWNER_FIRST_NAME || ', ' || owner.OWNER_LAST_NAME AS "Owner Name"
        , vw.RESERVATION_START_DATE AS "Start Date"
        , vw.RESERVATION_END_DATE AS "End Date"
        , vw.RESERVATION_END_DATE - vw.RESERVATION_START_DATE AS "Number of Days"
        , SUM(vw.DAILY_RATE) AS "Invoice Total"
FROM HVK_RESERVATION_CHARGES_VW vw, HVK_OWNER owner
WHERE owner.OWNER_NUMBER = vw.OWNER_NUMBER
AND vw.RESERVATION_NUMBER = vw.RESERVATION_NUMBER
GROUP BY vw.RESERVATION_NUMBER;

score 1 · Answer 1 · answered Dec 11 '14 at 03:42

In Oracle (as with most databases), you have to aggregate by all columns not in aggregation functions:

SELECT vw.RESERVATION_NUMBER AS "Reservation Number",
       owner.OWNER_FIRST_NAME || ', ' || owner.OWNER_LAST_NAME AS "Owner Name",
       vw.RESERVATION_START_DATE AS "Start Date", vw.RESERVATION_END_DATE AS "End Date", 
       (vw.RESERVATION_END_DATE - vw.RESERVATION_START_DATE) AS "Number of Days",
       SUM(vw.DAILY_RATE) AS "Invoice Total"
FROM HVK_RESERVATION_CHARGES_VW vw JOIN
     HVK_OWNER owner
     ON owner.OWNER_NUMBER = vw.OWNER_NUMBER AND vw.RESERVATION_NUMBER = vw.RESERVATION_NUMBER
GROUP BY vw.RESERVATION_NUMBER, owner.OWNER_FIRST_NAME || ', ' || owner.OWNER_LAST_NAME,
         vw.RESERVATION_START_DATE, vw.RESERVATION_END_DATE;

However, given all the aggregation columns, I'm not so sure that aggregation is even needed for this query.

score 0 · Answer 2 · answered Dec 11 '14 at 03:41

Put all the fields from the select statement in the group by clause that aren't included in an aggregate:

...
GROUP BY vw.RESERVATION_NUMBER, 
         owner.OWNER_FIRST_NAME || ', ' || owner.OWNER_LAST_NAME, 
         vw.RESERVATION_START_DATE, 
         vw.RESERVATION_END_DATE

score 0 · Answer 3 · answered Dec 11 '14 at 05:30

The others have identified your syntax error which is causing the compilation to fail. So I will point out the logical failure in your code: what you have written does not calculate the cost of the stay. You need to multiply the number of days by the daily rate:

(vw.RESERVATION_END_DATE - vw.RESERVATION_START_DATE) 
                 * vw.DAILY_RATE AS "Invoice Total"

This means you don't need the SUM() and so you can dispense with the GROUP BY clause altogether.

Oracle SQL - not a GROUP BY function

3 Answers3