CODEWITHSUNDEEP
mysqlsqlsum
timenz
timenz

Reputation: 37

MySQL query order by SUM field of another table

I have 3 table :

hotel : id, hotel_name

hotel_supplier:id, supplier_name

hotel_price:id, hotel_id, supplier_id, allotment, price

I want to select all from hotel order by sum(allotment) of hotel price descending, so the result sort from hotel that have the most allotment. How to do this ..?, any ideas gratefully received..., thank.

sorry, i am forget one thing, there is another condition, on hotel_price if price == 0 then allotment of the row is not count in sum(allotment).

Upvotes: 0

Views: 1189

Answers (1)

Andrew Logvinov
Andrew Logvinov

Reputation: 21831

Try this variant:

select h.id, h.hotel_name, sum(hp.allotment)
  from hotel h join hotel_price hp on (h.id = hp.hotel_id)
 group by h.id, h.hotel_name
 order by 3 desc;

Upvotes: 4

Related Questions