SQL problem.
I have a database table called Booking. There are 4 fields in it: booking_id, date, time, and doctor_id and booking_id is the primary key.
The doctor_id is the primary key of the Doctor table.
Now I am want to List out the booking record in the Booking table, and group by date, time and doctor_id together. It should count how many booking is happening for the same date, same time and same doctor_id.
For example:
The booking record have 3 items
booking_id | date | time | doctor_id |
1 | 2020-4-27 | 5:45 | 1 |
2 | 2020-4-27 | 5:45 | 1 |
3 | 2020-4-26 | 5:45 | 3 |
I expect the output
date | time | waiting_number |
2020-4-27 | 5:45 | 2 |
2020-4-26 | 5:45 | 1 |
Solved 1 Answer
See More Answers for FREE
Enhance your learning with StudyX
Receive support from our dedicated community users and experts
See up to 20 answers per week for free
Experience reliable customer service
/* give feedback if any correction required*/ CREATE TABLE Doctor (doctor_id integer PRIMARY KEY, doctor_name varchar(20)); INSERT INTO Doctor  VALUES(1,'XYZ'); INSERT INTO Doctor  VALUES(2,'QWE'); INSERT INTO Doctor  VALUES(3,'RTY'); CREATE TABLE Booking (booking_id integer PRIMARY KEY, date Date,time Time,doctor_id integer, FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)); INSERT INTO Booking  VALUES(1,'2020-4-27','5:45',1); INSERT INTO Booking  VALUES(2,'2020-4-27','5:45',1); INSERT INTO Booking  VALUES(3,'2020-4-26','5:45',2); select date,time,Count(*) as waiting_number,Booking.doctor_id from Booking  INNER JOIN Doctor ON Booking.doctor_id = Doctor.doctor_id group by date, Booking.doctor_id,time Answer question ...