CODEWITHSUNDEEP
phpmysqlsql
Spencer Lim
Spencer Lim

Reputation: 29

Counting field total against another table column value

room      | beds available | ****table room****
==================================
room1     | 4
room2     | 2 
room3     | 4

room      | occupant       | ****table occupant****
==================================
room1     | arnold
room1     | berry
room2     | charles
room2     | daisy
room3     | eric
room3     | frank
room3     | greg

I looking to get the following output:

No. of rooms with beds available: 2 || Rooms with beds available: room 1, room 3

i reckon i need to

  1. store count* for each unique room and store the count as an array
  2. subtract beds available for each room against this array
  3. display the room name when there's a result > 0

How should the php snippet code look like?

Upvotes: 1

Views: 69

Answers (4)

James
James

Reputation: 106

These SQL queries will do the trick.

Remember, you can remove unwanted columns to save the amount of data you're processing...

select r.id AS 'room',
    r.beds as 'total_beds',
    count(o.occupant) as 'taken_beds',
    r.beds-count(o.occupant) as 'free_beds'

FROM room r LEFT JOIN occupant o ON r.id = o.room
GROUP BY r.id

HAVING r.beds > count(o.occupant)

This will only return the rooms that are not full.

MySQL Result

If at any other point, you wish to return full rooms too, simply remove the "HAVING" clause

select r.id AS 'room',
    r.beds as 'total_beds',
    count(o.occupant) as 'taken_beds',
    r.beds-count(o.occupant) as 'free_beds'

FROM room r LEFT JOIN occupant o ON r.id = o.room
GROUP BY r.id

MySQL Result without HAVING clause

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

MySQL offers GROUP_CONCAT to aggregate strings:

select 
  count(*) as number_of_rooms, 
  group_concat(room) as rooms
from room r
where beds_available >
(
  select count(*)
  from occupant o
  where o.room = r.room
);

This selects rooms with more beds available than occupied and then aggregates the resulting rows to one row containing the number of available rooms and a string with the room names comma-separated.

Upvotes: 0

Bonewolf
Bonewolf

Reputation: 149

So something like this where you can change how many available beds you need..

SELECT r.*, COUNT(*) AS occupied, r.beds - COUNT(*) AS rest
FROM room r
LEFT JOIN occupant o ON r.room = o.room
GROUP BY r.room
HAVING r.beds - occupied >= 1

Upvotes: 0

apriede
apriede

Reputation: 106

I think this all can be done with a simple SQL query that should look something like this:

SELECT room.beds AS beds, COUNT(occupant.occupant) AS beds_occupied
FROM room
LEFT JOIN occupant ON occupant.room = room.room
GROUP BY room
HAVING (beds - beds_occupied) > 0;

You can use the 'HAVING' clause which is similar to the WHERE clause but works with aggregates. I haven't run this exact query on your exact tables so there might be a typo, however, I hope the idea what the query is supposed to do is clear.

Upvotes: 0

Related Questions