0

SQL Error (1055): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.room id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT hotel_id,room_id,room_number,MAX(a.tc) AS "Count",MAX(tp) AS   "MostProfit" FROM
(SELECT hotel_id,rooms.room_id,room_number,COUNT(rooms.room_id) AS  "tc",SUM(room_price) AS "tp" FROM rooms JOIN bookings 
ON rooms.room_id = bookings.room_id
GROUP BY rooms.room_id) a GROUP BY hotel_id

trying to get the rooms of hotels that got the most profit in a query

Abood 4433
  • 19
  • 5
  • You really need to explain what you want to do. It might surprise you, but a non-functioning query does not necessary express what you want to do. – Gordon Linoff Apr 06 '19 at 22:52
  • its query Get room which made the most profits for each hotel – Abood 4433 Apr 06 '19 at 22:55
  • https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc the answer is useless and does not work – Abood 4433 Apr 06 '19 at 23:06

1 Answers1

0

The error message implies that you need to convert your group by clause to 

GROUP BY hotel_id, room_id, room_number

since the other columns tc and tp are aggregated, and no need to be included in the group by list, whereas room_id and room_number are not.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55