CODEWITHSUNDEEP
phpsqlcodeigniter
moses toh
moses toh

Reputation: 13172

How to count the number of each type of room and total room?

My query is like this :

SELECT name, room_type, room_number FROM table_room

The result is like this : enter image description here

I want to add a description like this : 

Single Room : 3 Room
Double Room : 2 Room
Family Room : 1 Room
Total  : 6 room

additional information :

Single Room 1 : Tony
Single Room 2 : Eden
Single Room 3 : Christiano
Double Room 1 : Wayne and Christina
Double Room 2 : Ryan and Jose
Family Room 1 : David, Peter and Carlo

Total : 6 rooms, not 10 rooms

I have tried. But I am is still difficult.

Thank you.

To determine the number of rooms, it is not based on room type, but the room number

So based on the table above should result like this:

Single Room : 3 Room
Double Room : 2 Room
Family Room : 1 Room
Total  : 6 room

additional information :

Single Room 1 : Tony
Single Room 2 : Eden
Single Room 3 : Christiano
Double Room 1 : Wayne and Christina
Double Room 2 : Ryan and Jose
Family Room 1 : David, Peter and Carlo

Total : 6 rooms, not 10 rooms

Sorry if my question is less clear

Thank you very much

Upvotes: 0

Views: 3456

Answers (5)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

depend on your rdbms you can construct the string before return it

I include a column order_id so I can order and show total on last row.

SELECT * 
FROM (
  SELECT 1 order_id, room_type + ': ' +  Count(*) + ' Room' as desc
  FROM table_room
  GROUP BY room_type
  UNION ALL
  SELECT 2 order_id, 'Total : ' +  Count(*) + ' Room' as desc
  FROM table_room
)
ORDER BY order_id

NOTE: You can't guarantee the result order unless you include ORDER BY

NOTE 2: The best way is you ask your query return only group total

| Double Room |        4 |
| Family Room |        3 |
| Single Room |        3 |

And calculate the total in the php UI. That way your query only have one type of data and doesn't have to worry about order

Upvotes: 0

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

SQL Fiddle

SELECT room_type, COUNT(*) AS total
FROM table_room
GROUP BY room_type
UNION ALL
SELECT 'Total' AS room_type, COUNT(*) AS total
FROM table_room;

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37079

You can get the desired result with:

select room_type, count(*) as roomcount from test group by room_type
union all
select 'Total', count(*) as roomcount from test

Result:

+-------------+----------+
| room_type   | count(*) |
+-------------+----------+
| Double Room |        4 |
| Family Room |        3 |
| Single Room |        3 |
| Total       |       10 |
+-------------+----------+

You can retrieve this result with PHP and format it to your liking.

If you are using MySQL, you could even do something like this to get formatted output directly from the database like this:

select concat(
  room_type, ' : ', roomcount,
  case when roomcount < 2 then ' Room' else ' Rooms' end
) as outputstring
from (
select room_type, count(*) as roomcount from test group by room_type
union all
select 'Total', count(*) as roomcount from test
) sub_query

Result:

+-----------------------+
| outputstring          |
+-----------------------+
| Double Room : 4 Rooms |
| Family Room : 3 Rooms |
| Single Room : 3 Rooms |
| Total : 10 Rooms      |
+-----------------------+

Upvotes: 2

Subin Chalil
Subin Chalil

Reputation: 3660

select room_type, count(*) as roomcount from test group by room_type
UNION
select 'Total' as room_type , count(*) as roomcount from test

Hope this helps.

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Use count or group by

SELECT count(*) as total,room_type FROM table_room group by 
room_type

For total room :-

 SELECT count(*) as total_room FROM table_room

In singal query :-

SELECT count(*) as total,room_type,
(SELECT count(*) FROM table_room) as total_room FROM table_room group by 
room_type

Upvotes: 0

Related Questions