CODEWITHSUNDEEP
mysql
ashirbad
ashirbad

Reputation: 35

Counting the number of fields in a table for a particular value

I have a table which has the following columns

dt_id,date,sl1,sl2,sl3,sl4,sl5,sl6,sl7,sl8.................sl24

The above are the structure for a allotment system in which in a particular day there are 24 available slots from sl1 to sl24 all of which have a default value of 1 which means that it is unreserved . Is there any way in which i could calculate the number of fields which have the value 1 . So that i can get the number of slots which are available for a particular day ?. PLEASE CORRECT ME IF MY SCHEMA IS WRONG . This is my first code.

Upvotes: 1

Views: 22

Answers (1)

abhinsit
abhinsit

Reputation: 3272

You should normalise your table to be in format:

dt_id,date,slot_id,slot_status

where for each date now you can define any number of slots (for current use case that can be 24)

Now to fetch the number of slots which are available can be done in simple query:

Select count(*) from table where slot_status = 1 and date = <your_date_here>

To get the available slots use:

Select * from table where slot_status = 1 and date = <your_date_here>

Upvotes: 1

Related Questions