I was making the database for some private site. In my database I have a table named times where I can save the usernames and the times when the user is free. The table looks like this.

|uid |Monday|Tuesday |... |Saturday |Sunday |t12|t13|t14|...|t19|t20
_____________________________________________________________________

The uid is the username and t12-t20 are the hours ( t16 = 1, monday = 1 would mean that the user is free on monday at 16).

The example of one row would be:

|alex1|1|1|0|0|0|0|1|0|0|0|0|0|1|1|1|1|

That would mean that the user alex1 is free on monday, tuesday and on sunday at 17, 18, 19 and 20 o'clock.

I can have multiple users in the table and multiple times for some users. Like, the user alex1 can be in more rows with different saved times. (Example: one row means that he is free on monday and friday after 15 but in some other row is he free on saturday at 14 and at 17 and at 20)

Example:

gordon | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
gordon | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 

First row would mean: User gordon is free at 12 o'clock on Monday and Tuesday second row means: User gordon is free at 13 o'clock on Wednesday

The question is:

How can I make a query that goes trough the table and gives me the result:

  Day    | t12 | t13 | t14 | t15 | t16 | t17 | t18 | t19 | t20 |
________________________________________________________________
Monday      5     4     8     8     8     4     0     0     4
Tuesday     2     2     2     2     2     1     0     0     2
Wednesday   10    10    10    2     8     10    8     7     7
Thursday    5     4     8     8     8     4     0     1     4
Friday      5     4     3     7     8     4     9     0     4
Saturday    8     9     8     3     9     4     3     0     4
Sunday      1     4     8     8     1     4     0     9     3

That would mean: On monday at 14 o'clock there are 8 users that are free, or on friday at 20 there are 4 users which are free.

2 Answers

1
Strawberry On

One example of a better design would be to have a table as follows:

availability

user day time
   1   1   16

This might not be the best solution, but it's a vast improvement on your current setup.

-1
mkRabbani On

you can calculate day wise results and then use union all as below-

SELECT 'Monday' AS Day,
SUM(t12) t12,SUM(t13) t13,SUM(t14) t14,SUM(t15) t15,SUM(t16) t16,
SUM(t17) t17,SUM(t18) t18,SUM(t19) t19,SUM(t20) t20
FROM your_table WHERE Monday = 1

UNION ALL

SELECT 'Tuesday' AS Day,
SUM(t12) t12,SUM(t13) t13,SUM(t14) t14,SUM(t15) t15,SUM(t16) t16,
SUM(t17) t17,SUM(t18) t18,SUM(t19) t19,SUM(t20) t20
FROM your_table WHERE Tuesday = 1

UNION ALL

SELECT 'Wednesday' AS Day,
SUM(t12) t12,SUM(t13) t13,SUM(t14) t14,SUM(t15) t15,SUM(t16) t16,
SUM(t17) t17,SUM(t18) t18,SUM(t19) t19,SUM(t20) t20
FROM your_table WHERE Wednesday = 1

UNION ALL

SELECT 'Thursday' AS Day,
SUM(t12) t12,SUM(t13) t13,SUM(t14) t14,SUM(t15) t15,SUM(t16) t16,
SUM(t17) t17,SUM(t18) t18,SUM(t19) t19,SUM(t20) t20
FROM your_table WHERE Thursday = 1

UNION ALL

SELECT 'Friday' AS Day,
SUM(t12) t12,SUM(t13) t13,SUM(t14) t14,SUM(t15) t15,SUM(t16) t16,
SUM(t17) t17,SUM(t18) t18,SUM(t19) t19,SUM(t20) t20
FROM your_table WHERE Friday = 1

UNION ALL

SELECT 'Saturday' AS Day,
SUM(t12) t12,SUM(t13) t13,SUM(t14) t14,SUM(t15) t15,SUM(t16) t16,
SUM(t17) t17,SUM(t18) t18,SUM(t19) t19,SUM(t20) t20
FROM your_table WHERE Saturday = 1

UNION ALL

SELECT 'Sunday' AS Day,
SUM(t12) t12,SUM(t13) t13,SUM(t14) t14,SUM(t15) t15,SUM(t16) t16,
SUM(t17) t17,SUM(t18) t18,SUM(t19) t19,SUM(t20) t20
FROM your_table WHERE Sunday = 1