SQL error - ORA-00933: SQL command not properly ended

Question

I'm aware that there are numerous questions similar to this that have been asked before, but I've looked through many of them and I haven't been able to find a solution to my following problem.

This is my attempt at writing a query that displays the total number of reservations by room type in the month of March:

 select rt.room_type_id as RoomType
      , count(r.no_of_reservations) as NoOfReservations
 from Room_type rt
    , Room r
    , Reservation res
where rt.room_type_id = r.room_type_id
  and r.room_id = res.room_id
  and extract(month from res.check_in_date) = 3
  and extract(month from res.check_out_date) = 3
order by r.no_of_reservations
group by r.room_type_id;

When I run this query, I get the error: ERROR at line 11: ORA-00933: SQL command not properly ended.

How do I fix this? I'm not sure what's wrong.

@NickKrasnov I made that change and got the error: ORA-00979: not a GROUP BY expression. — lefrost
The sequence of writing and SQL query is. 1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. ORDER BY — Waqas Shabbir
Moreover wherever there is a GROUP BY clause. You should use only group functions or the column the column(s) on which you have grouped by. In your case it is rt.room_type_id instead of r.room_type_id — Waqas Shabbir

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2018-08-05T14:08:40

You could use:

select r.room_type_id as RoomType       -- rt alias changed to r to match group by
      ,count(r.no_of_reservations) as NoOfReservations
from Room_type rt                        -- explicit join
join Room r
  on rt.room_type_id = r.room_type_id
join Reservation res
  on r.room_id = res.room_id
where extract(month from res.check_in_date) = 3
  and extract(month from res.check_out_date) = 3
group by r.room_type_id       --swapped lines group by <=> order by
order by NoOfReservations;    --alias from SELECT

SQL error - ORA-00933: SQL command not properly ended

1 Answers