help with this simple query

[achiman]

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

[urtrivedi]

Post your table structure and sample data

[debasisdas]

what is your table structure ?

[achiman]

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

[debasisdas]

in which table you have stores the number of room ?

i can't fine that from your table structure.

[achiman]

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

[debasisdas]

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

[achiman]

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

[debasisdas]

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