COUNT and SELECT into the same query in mySQL

Question

Form my two tables, I want to select SUB_Limit and count the number of rooms (only the active one).

So the query should returns me something like this:

|-----------|------------|-------------|
| SUB_Limit | ROO_Number | ROO_HotelId |
|-----------|------------|-------------|
|        10 |          0 |           1 |
|        15 |          3 |           2 |
|         5 |          2 |           3 |
|        25 |          0 |           4 |
|-----------|------------|-------------|

Why this query do not return me the desired output please ?

SELECT 
    ROO_HotelId, 
    SUB_Limit, 
    COUNT(ROO_Id) AS ROO_Number
FROM ___Rooms
LEFT JOIN ___Subscriptions
    ON ___Rooms.ROO_HotelId = ___Subscriptions.SUB_HotelId
WHERE ROO_Status = 'active'
    AND SUB_HotelId = 1
    AND ROO_HotelId = 1

Actually, it gave me:

|-----------|------------|
| SUB_Limit | ROO_Number |
|-----------|------------|
|        15 |          3 |
|         5 |          2 |
|-----------|------------|

So I haven't the ___Subscriptions with no rooms.

Here the SQL Fiddle for help.

Thanks in advance.

this is basically because there's nothing in the Rooms table with hotelID 1 — Javier Larroulet
– Javier Larroulet, Commented Aug 30, 2018 at 16:54
@JavierLarroulet, yes it the case here. But how I can handle this? Thanks. — PacPac
– PacPac, Commented Aug 30, 2018 at 17:31
you could do Subscriptions LEFT JOIN Rooms (not the same as Rooms RIGHT JOIN Subscriptions) and handle the case when the rightmost side of the join returns null via code. That's how I'd do it — Javier Larroulet
– Javier Larroulet, Commented Aug 30, 2018 at 19:05

Raptord · Accepted Answer · 2018-08-30 17:50:25Z

1

The query below gives you the result you want.

SELECT 
  SUB_HotelId,
  SUB_Limit, 
  COUNT(ROO_Id) AS ROO_Number  
FROM ___Subscriptions
LEFT JOIN ___Rooms ON 
  ___Rooms.ROO_HotelId = ___Subscriptions.SUB_HotelId
WHERE ROO_Status = 'active' or ROO_Status is null
GROUP BY SUB_HotelId

You need to check for ROO_Status = 'active' or NULL because if the subscription is not linked to a room, there will be no value for ROO_Status. Also, you need to group on SUB_HotelId, because ROO_HotelId will also be null if there is no room to join the subscription to.

Link to fiddle: http://sqlfiddle.com/#!9/c6920c/35

answered Aug 30, 2018 at 17:50

Raptord

5033 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

COUNT and SELECT into the same query in mySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related