CODEWITHSUNDEEP
phpmysqli
jamseernj
jamseernj

Reputation: 1052

Get sum of more than one table columns with a single query

I have a main table hotel and its sub tables hotel_food, hotel_extras, hotel_room with the reference Id hotel,

And now I want to get the sum of price column in each table (hotel_food, hotel_extras, hotel_room) for each hotels.

Now I am using like this

$result = $db->query("SELECT id FROM hotel WHERE id = '.$hotel.'");
if($row = $result->fetch_array(MYSQLI_ASSOC)) {
   $food_result = $db->query("SELECT sum(price) FROM hotel_food WHERE hotel_id = '".$row['id']."'");
   $room_result = $db->query("SELECT sum(price) FROM hotel_room WHERE hotel_id = '".$row['id']."'");
   $extras_result = $db->query("SELECT sum(price) FROM hotel_extras WHERE hotel_id = '".$row['id']."'");
}

Is there any way to get the sum of all tables prices like totalFoodPrice, totalRoomPrice,totalExtrasPrice with a single query

Thanks in advance

Upvotes: 0

Views: 56

Answers (2)

MansoorShiraz
MansoorShiraz

Reputation: 118

You can make use of the subqueries like this :

$result = $db->query("SELECT id, 
   (SELECT sum(price) FROM hotel_food WHERE hotel_id = h.id) TotalFoodPrice,
   (SELECT sum(price) FROM hotel_room WHERE hotel_id = h.id) TotalRoomPrice,
   (SELECT sum(price) FROM hotel_extras WHERE hotel_id = h.id) TotalExtraPrice
FROM hotel h WHERE id = '.$hotel.'");

Upvotes: 1

Hanky Panky
Hanky Panky

Reputation: 46900

SELECT * FROM
(
 SELECT sum(price) AS totalFoodPrice FROM hotel_food WHERE hotel_id = 1,
 SELECT sum(price) AS totalRoomPrice FROM hotel_room WHERE hotel_id = 1,
 SELECT sum(price) AS totalExtrasPrice FROM hotel_extras WHERE hotel_id = 1
) temp

Upvotes: 1

Related Questions