CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Scott
Scott

Reputation: 85

Two Queries into one table with two columns

I have a table named Rooms that looks like this

rooms

id    name   room number 
-------------------------
1     Dave   Room 100  
2     NULL   ROOM 101    
3     Scott  Room 100

I need to check which room is empty and which room has people associated with it so the result is going to be :

Room name  Count        
-----------------
Room 100   2      -- Because Dave and Scott is in the room
Room 101   0      -- No name is associated with the room

I just started learning SQL so my knowledge is limited What i can see is

SELECT Count(room_number) 
  FROM rooms 
 WHERE name != NULL

...will return 2 and 0 based on times of room_number shows up in a list and the name associated with it is not 0 and also

SELECT DISTINCT(room_number) 
  FROM rooms

...will return Room 100 and Room 101 once

How can I combine these two queries to give me the result table that I want?

Upvotes: 1

Views: 211

Answers (2)

Hector Sanchez
Hector Sanchez

Reputation: 2317

SELECT COUNT(NAME), RoomNumber
FROM ROOMS
GROUP BY RoomNumber

Upvotes: 0

Dustin Laine
Dustin Laine

Reputation: 38503

SELECT RoomNumber, COUNT(Name)
FROM rooms
GROUP BY RoomNumber

I would recommend adding a Room table and changing RoomNumber to a foreign key relationship. Normalize that column.

Upvotes: 4

Related Questions