CODEWITHSUNDEEP
mysqlcount
kapitanluffy
kapitanluffy

Reputation: 1267

mysql compare column with count()

I have two tables. One for Customers and another for Rooms. What I want to retrieve the rooms using mysql with the following requirements..

  1. the number of customers in a specific room is less than the head from the rooms table
  2. the room is not in the customers table

you may check out this paste http://pastebin.com/WgTtkQvD

as you can see only room 1 is not in the expected result because its 'head' and the total customers in that room is equal

room 2 has 3 customers which is less than the 'head' of room 2

rooms 3 and 4 are in the expected result because no one has 'occupied it yet

Upvotes: 2

Views: 2045

Answers (4)

arunmoezhi
arunmoezhi

Reputation: 3200

Let me know if this works

select r.id,c.occ_cnt as occupied,r.head as total_head,r.head - c.occ_cnt as       remaining_head
from room r,
(
select room_id,count(id) as occ_cnt
from customer
group by room_id
) c
where r.id = c.room_id

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this

select 
    r.id,coalesce(ct.counting,0) as occupied,r.head as total_head,
    r.head-coalesce(ct.counting,0)  as remaining_head
from
    rooms as r left join
(
    select room_id,count(id) as counting from customer as ct 
    group by room_id
) as ct on r.id=ct.room_id
where r.head-coalesce(ct.counting,0)>0

Upvotes: 0

eggyal
eggyal

Reputation: 125865

SELECT   Rooms.id AS room_id,
         COUNT(Customer.id) AS occupied,
         Rooms.head AS total_head,
         Rooms.head - COUNT(Customer.id) AS remaining_head
FROM     Rooms LEFT JOIN Customer ON Customer.room_id = Rooms.id
GROUP BY Rooms.id
HAVING   remaining_head > 0

See it on sqlfiddle.

Upvotes: 5

Borislav Sabev
Borislav Sabev

Reputation: 4856

The following should do the trick:

SELECT r.id, COUNT(c.id), r.head, r.head - COUNT(c.id) FROM Rooms AS r LEFT JOIN Customer AS c ON r.id = c.room_id WHERE r.head <= COUNT(c.id) GROUP BY r.id

I didn't fully understand your question and I haven't actually tried this on a real DB, so there may be some glitches to sort out. Comment it any more questions arise.

Upvotes: 0

Related Questions