CODEWITHSUNDEEP
mysql
PacPac
PacPac

Reputation: 261

COUNT and SELECT into the same query in mySQL

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.

Upvotes: 0

Views: 42

Answers (1)

Raptord
Raptord

Reputation: 503

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

Upvotes: 1

Related Questions