CODEWITHSUNDEEP
sqloracle-databasegroup-by
Za K
Za K

Reputation: 3

"not a group by expression"

I'm a SQL noob that's trying to solve this group by issue. When I run it it throws me "not a group by expression". I've tried omitting fields, removing column heading syntax and it still wont run. I'm at lost as to where the issue is. Let me know if you'd like me to reformat into something prettier! 

SELECT owner.owner_first_name, 
       owner.owner_last_name                 AS NAME, 
       owner.owner_phone                     AS PHONE, 
       pet.pet_name, 
       Sum(res.reservation_end_date - res.reservation_start_date) AS NumDays 
FROM   hvk_pet pet 
       INNER JOIN hvk_dog dog 
           ON pet.pet_number = dog.pet_pet_number 
       INNER JOIN hvk_pet_reservation pRes 
           ON pRes.pet_pet_number = pet.pet_number 
       INNER JOIN hvk_reservation res 
           ON res.reservation_number = pRes.res_reservation_number 
       INNER JOIN hvk_owner owner 
           ON owner.owner_number = pet.own_owner_number 
GROUP  BY owner.owner_first_name, 
      owner.owner_last_name, 
      owner.owner_phone, 
      pet.pet_name 
HAVING res.reservation_end_date - To_date('30-NOV-15', 'dd/mm/yy') <= 0 
       AND Sum(res.reservation_end_date - res.reservation_start_date) >= ALL 
       (SELECT Sum(res.reservation_end_date - res.reservation_start_date) 
        FROM 
           hvk_reservation res)

Upvotes: 0

Views: 84

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Well, your error is coming from the following:

HAVING res.reservation_end_date - To_date('30-NOV-15', 'dd/mm/yy') <= 0

res.reservation_end_date is not in the group by clause. Move that into the where criteria and your error should go away.

With that said, I'm not sure your query will ever return any results. You're checking to see if the sum of a subset of values is greater than the sum of all the values -- not likely.

Upvotes: 3

Related Questions