Question :
I have two queries in sql.
First query is:
select hotel_id , count(*) as NumberOfRooms
from room
group by hotel_id;
The result is:
# | hotel_id | NumberOfRooms
-----------------------------
1 | 1111 | 1
2 | 2222 | 2
3 | 3333 | 2
4 | 4444 | 1
Second query is:
select count(distinct client_id) as NumberOfClients
from payment;
The result is:
# | NumberOfClients
--------------------
1 | 4
2 | 2
3 | 3
4 | 4
What I need looks something like this:
# | hotel_id | NumberOfRooms |NumberOfClients |
-----------------------------------------------
1 | 1111 | 1 | 4 |
2 | 2222 | 2 | 2 |
3 | 3333 | 2 | 3 |
4 | 4444 | 1 | 4 |
And relationship model looks like this:
The task itself was to show for each hotel number of rooms in hotel and number of distinct clients that have been in hotel.
They both work fine, but because they are for two different tables I don’t know how to properly join them into single query.
Any help would be much appreciated.
Here are table structures:
CREATE TABLE "RIADORA"."PAYMENT"
( "CLIENT_ID" NUMBER(20,6) NOT NULL ENABLE,
"PAYMENT_ID" NUMBER(20,6) NOT NULL ENABLE,
"ROOM_ID" NUMBER(20,6) NOT NULL ENABLE,
"PAYMENT_DATE" DATE NOT NULL ENABLE,
"PERIOD" NUMBER(20,6) NOT NULL ENABLE,
"SUMM" NUMBER(20,6) NOT NULL ENABLE,
"PERSONAL_ID" NUMBER(20,6) NOT NULL ENABLE,
CONSTRAINT "PAYMENT_PK" PRIMARY KEY ("PAYMENT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "CLIENTP" FOREIGN KEY ("CLIENT_ID")
REFERENCES "RIADORA"."CLIENT" ("CLIENT_ID") ENABLE,
CONSTRAINT "ROOMP" FOREIGN KEY ("ROOM_ID")
REFERENCES "RIADORA"."ROOM" ("ROOM_ID") ENABLE,
CONSTRAINT "PERSONALP" FOREIGN KEY ("PERSONAL_ID")
REFERENCES "RIADORA"."PERSONAL" ("PERSONAL_ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
COMMENT ON COLUMN "RIADORA"."PAYMENT"."CLIENT_ID" IS 'ID number of client';
COMMENT ON COLUMN "RIADORA"."PAYMENT"."PAYMENT_ID" IS 'ID number of payment';
COMMENT ON COLUMN "RIADORA"."PAYMENT"."ROOM_ID" IS 'ID number of room';
COMMENT ON COLUMN "RIADORA"."PAYMENT"."PAYMENT_DATE" IS 'Date when payment occured';
COMMENT ON COLUMN "RIADORA"."PAYMENT"."PERIOD" IS 'Period of days, during which client stays at hotel';
COMMENT ON COLUMN "RIADORA"."PAYMENT"."SUMM" IS 'Amount of money paid';
COMMENT ON COLUMN "RIADORA"."PAYMENT"."PERSONAL_ID" IS 'ID number of personal';
COMMENT ON TABLE "RIADORA"."PAYMENT" IS 'Payment data';
and
CREATE TABLE "RIADORA"."ROOM"
( "PRICE" NUMBER(20,6) NOT NULL ENABLE,
"ROOM_SIZE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"ROOM_ID" NUMBER(20,6) NOT NULL ENABLE,
"HOTEL_ID" NUMBER(20,6) NOT NULL ENABLE,
CONSTRAINT "ROOM_PK" PRIMARY KEY ("ROOM_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "HOTEL_ID" FOREIGN KEY ("HOTEL_ID")
REFERENCES "RIADORA"."HOTEL" ("HOTEL_ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
COMMENT ON COLUMN "RIADORA"."ROOM"."PRICE" IS 'Rooms price';
COMMENT ON COLUMN "RIADORA"."ROOM"."ROOM_SIZE" IS 'Size of room';
COMMENT ON COLUMN "RIADORA"."ROOM"."ROOM_ID" IS 'ID number of room';
COMMENT ON COLUMN "RIADORA"."ROOM"."HOTEL_ID" IS 'ID number of hotel';
COMMENT ON TABLE "RIADORA"."ROOM" IS 'Rooms data';
Answer :
By Joining the two:
select hotel_id,
count(*) as NumberOfRooms,
count(distinct client_id) as NumberOfClients
from room
join payment on room.room_id = payment.room_id
group by hotel_id;