SQL Statement to Group results by two rows to create a timetable

Question

I currently have a database table which records the bookings of jobs and there are 8 timeslots available

+-----------+
|tbl_booking|
+-----------+
|room_id    |
|date       |
|timeslot   |
|booking    |
+-----------+


sample data
+-----------+----------+-----------+
|room_id    | date     | timeslot  |
+-----------+----------+-----------+
|1          |2018-01-01|  1        |
|1          |2018-01-01|  2        |
|1          |2018-01-01|  4        |
|2          |2018-01-01|  1        |
+-----------+----------+-----------+

intended outcome - when statement filters for bookings on 2018-01-01
+-----------+----------+-----------+----------+-----------+
|room       |timeslot1 | timeslot2 |timeslot3 | timeslot4 |
+-----------+----------+-----------+----------+-----------+
|1          |    X     |     X     |          |    X      |
|2          |    X     |           |          |           |
+-----------+----------+-----------+----------+-----------+

i started off with this statement:

SELECT * from tbl_booking WHERE date = '2018-01-01' GROUP BY room_id

and this would return results to see the results grouped by rooms. I would like to know where i should go from here to also have the results display it's timeslots that are shown in a table displaying the booking status of eacah room's timeslot in the day?

Should there be an SQL statement that i should be using or am I on the wrong track completely?

Any help would be appreciated. Thank you!

update your question add a proper data sample and the expected result — ScaisEdge, Jan 01 '18 at 19:09
Without seeing data & desired result.. All i can assume is that either you don't need the group by (in fact, that query right now wouldn't even work) as it'll expand the timeslots per date out or you need to `GROUP BY room_id, timeslot` — Matt, Jan 01 '18 at 19:13
my apologies , i have updated the question to include sample data and the intended outcome of the query — user3448267, Jan 01 '18 at 19:16

Matt · Accepted Answer · 2018-01-01T20:43:49.427

1

What you want to do with the data isn't nice to do and if you have a fixed number of time slots then you can hardcode the columns like this:

SELECT room_id,
       SUM(CASE WHEN timeslot = 1 then 1 else 0 END) AS Timeslot1,
       SUM(CASE WHEN timeslot = 2 then 1 else 0 END) AS Timeslot2,
       SUM(CASE WHEN timeslot = 3 then 1 else 0 END) AS Timeslot3,
       SUM(CASE WHEN timeslot = 4 then 1 else 0 END) AS Timeslot4
 FROM tbl_booking
 GROUP BY room_id

(see SQL Fiddle)

You could use MAX if you just want to see if at least 1 booking exist

SQL to include remark, you can trick it to select the remark through a group by with MAX

SELECT room_id,
       SUM(CASE WHEN timeslot = 1 then 1 else 0 END) AS Timeslot1,
       MAX(CASE WHEN timeslot = 1 THEN remark ELSE '' END) AS Timeslot1Remark,
       SUM(CASE WHEN timeslot = 2 then 1 else 0 END) AS Timeslot2,
       MAX(CASE WHEN timeslot = 2 THEN remark ELSE '' END) AS Timeslot2Remark,
       SUM(CASE WHEN timeslot = 3 then 1 else 0 END) AS Timeslot3,
       MAX(CASE WHEN timeslot = 3 THEN remark ELSE '' END) AS Timeslot3Remark,
       SUM(CASE WHEN timeslot = 4 then 1 else 0 END) AS Timeslot4,
       MAX(CASE WHEN timeslot = 4 THEN remark ELSE '' END) AS Timeslot4Remark
 FROM tbl_booking
 GROUP BY room_id

your extended SQL Fiddle. This won't work for multiple dates only 1 selected date

edited Jan 01 '18 at 20:43

answered Jan 01 '18 at 19:22

Matt

1,749
2
12
26

Well, to be fair, the OP did include PHP in the tags. – Strawberry Jan 01 '18 at 19:41
thanks for the code, this seems to work for the purpose just right. can this code be modified to accommodate a remark field that will be displayed next to the booking as well? i could always use some php to format the output from there – user3448267 Jan 01 '18 at 19:44
@user3448267 if you can confirm that the data will only have 1 booking per date per timeslot then you can add `GROUP BY room_id, remark` and change select to include remark also – Matt Jan 01 '18 at 19:56
@Matt yea there will only be a maximum of one booking per timeslot/day... i did some edits to the SQL fiddle to include that ---http://sqlfiddle.com/#!9/077420/1 , i m not good with these sql statements and i think i m doing something wrong here as the grouping seems to be off now – user3448267 Jan 01 '18 at 20:17
@Matt Thanks! Everything seems to be what i need for now, my last issue is wanting even more field! I currently just duplicate the MAX(CASE WHEN timeslot = 1 THEN date ELSE '' END) AS Timeslot1Date line, is there a better way to do this though? – user3448267 Jan 01 '18 at 21:16
@user3448267 yes, if you want it to be a bit more dynamic you'll need to look at pivoting the data, ideally if you are just getting it into this format for display you may want to do this logic in php rather than sql – Matt Jan 01 '18 at 21:34

Savaliya Bhavesh · Answer 2 · 2018-01-01T19:50:16.023

-1

If you are looking for dynamic solutions then you must need to use pivot table.

Select * from (select * from yourtable) as Temptable Pivot ( Count(room) For timeslot In (list of timeslot)) As tempSlot

For more information check this link MySQL pivot table

edited Jan 01 '18 at 19:50

answered Jan 01 '18 at 19:41

Savaliya Bhavesh

24
5

SQL Statement to Group results by two rows to create a timetable

2 Answers2