Please how can I write a query to select list of building its building code and number of rooms in each building.

Post your table structure and sample data

what is your table structure ?

I am sorry about my late respond, the table structure is shown below thanks very much for your time

EMPLOY TITLE SURNAME FORENAME1 FORENAME2 FORENAME3 ADDRESS1
------ --------- --------------- ------------ ------------ ------------ -------------
AS3300 MR SILVER ALEX 67 NORTH RD
AS3301 MRS JOHNSON FRED 67 NORTH RD
AS3302 MISS STONE LUCY 55 NORTH RD

BUILDING_CODE BUILDING_NAME LOCATION
- --------------- ---------
A AMSLY 123
B BJFTB 223
C CGHYE 323

ROOM_CODE BUILDING_CODE WIDTH LENGTH HEIGHT SEATS BLINDS VEND D DEFECT_DESC
--- - ------- ------- ------- ------- - - - ---------------
G02 A 10 10 5 55 Y H N
G03 A 10 10 3 60 Y H N
G02 B 10 10 3 120 Y H N
G03 B 10 10 5 20 Y H Y NO HOT WATER
G04 B 10 10 5 50 Y H Y NOT KNOWN
G05 B 10 10 5 50 Y H Y POWER SUPPLY
G01 C 10 10 6 70 Y H Y BROKEN SWITCH

BUILDIN_CODE ROOM_CODE BOOKING_DATE STAR FINI ATTENDEES R PURPOSE EMPLOYEE_CODE
- --- --------- ---- ---- --------- - ------------------------------ ------
A G01 25-MAR-09 1500 1600 20 N AS3300
B G01 25-MAR-09 1300 1400 20 N AS3301
C G01 25-MAR-09 1400 1700 20 N AS3302
A G02 26-MAR-09 1300 1500 20 N AS3303
A G01 26-MAR-09 1800 2000 20 N AS3301
A G01 27-MAR-09 1330 1500 20 N AS3302
A G01 27-MAR-09 1300 1800 20 N AS3303

in which table you have stores the number of room ?

i can't fine that from your table structure.

from the room_code in the third table shows the room e.g G02, G03, and the building_code shows the building the rooms are located

Number of rooms should be stored as numbers not strings in database table to perform any calculation on the same.

they are number but G02 is the name of the room(it mean ground floor room 2). this is just a sample data in the real data each room has a room_code which is unique to it. i think what i need is the number of rooms groupby the building name. Thanks very much

you need to store the number of rooms in a peparately column.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.