mySQL: how to select the day with more people off every week

Question

I have this database:

where numbers from 1 to 28 represents the day of the month(FEB) and 1 and 0 represents when user_bid_id is respectively off from work or not. I would like to select for every week (i.e.. 1 to 7, 8 to 15, 16 to 23 ect) the day with the maximum number of people off.

I have tried many different queries.

I can see who's available on that day and count the people of on that day with this function:

function checkPublishTraining($month){
     $month = sanitize($month);

if(bidIsPublished($month)){
 if(($month === 'FEB')){
  echo'
 <table>
 <tr id="checkBidding">
 <th>1</th> 
 <th>2</th> 
 <th>3</th> 
 <th>4</th> 
 <th>5</th> 
 <th>6</th> 
 <th>7</th> 
 <th>8</th> 
  <th>9</th> 
 <th>10</th> 
 <th>11</th> 
 <th>12</th> 
 <th>13</th> 
 <th>14</th> 
 <th>15</th> 
 <th>16</th> 
 <th>17</th> 
 <th>18</th> 
 <th>19</th> 
 <th>20</th> 
 <th>21</th> 
  <th>22</th> 
  <th>23</th> 
  <th>24</th> 
  <th>25</th> 
  <th>26</th> 
  <th>27</th> 
  <th>28</th> 
  </tr>
   <tr>';
          $i = 1;

    while ( $i <= 28 ) {
       $count = mysql_query("SELECT COUNT(`$i`)as days FROM $month WHERE `$i` = '1'");
       $num = mysql_fetch_array($count);

       echo'<td>';
       $names = mysql_query("SELECT user_bid_id FROM $month WHERE `$i` = '1'");
       while($row = mysql_fetch_array($names)){
       $name = firstName_from_id($row['user_bid_id']);
       echo '<h5>'.$name.'</h5>';
                    }

        echo'<h5>'.$num['days'].'</h5>';
         $i++;

       echo '</td>';

       }

       echo'</tr></table>';
       }
          }

             }

which return this table:

I can I choose now the day with more members but trying to give a minimum of one training session per month to every member?

Answer 1

To do this right you need to UNPIVOT the data, which MySQL doesn't support. So you can make a view for this that returns the unpivoted data using UNION ALL (view query below). After you have this view, you can do the following:

SELECT myView.week, myView.day
FROM (
  SELECT week, max(total) as total
  FROM myView
  GROUP BY week) s
JOIN myView
  ON s.week = myView.week
  AND s.total = myView.total

As noted in comments, you may have more than 1 result per week if several days have the same maximum for the week.

And here is the view query:

CREATE myView AS
SELECT 1 as week, 1 as day, SUM(`1`) as total
FROM tableName
UNION ALL
SELECT 1, 2, SUM(`2`)
FROM tableName
UNION ALL
SELECT 1, 3, SUM(`3`)
FROM tableName
UNION ALL
SELECT 1, 4, SUM(`4`)
FROM tableName
UNION ALL
SELECT 1, 5, SUM(`5`)
FROM tableName
UNION ALL
SELECT 1, 6, SUM(`6`)
FROM tableName
UNION ALL
SELECT 1, 7, SUM(`7`)
FROM tableName
UNION ALL
SELECT 2, 1, SUM(`8`)
FROM tableName
UNION ALL
SELECT 2, 2, SUM(`9`)
FROM tableName
UNION ALL
SELECT 2, 3, SUM(`10`)
FROM tableName
UNION ALL
SELECT 2, 4, SUM(`11`)
FROM tableName
UNION ALL
SELECT 2, 5, SUM(`12`)
FROM tableName
UNION ALL
SELECT 2, 6, SUM(`13`)
FROM tableName
UNION ALL
SELECT 2, 7, SUM(`14`)
FROM tableName
UNION ALL
SELECT 3, 1, SUM(`15`)
FROM tableName
UNION ALL
SELECT 3, 2, SUM(`16`)
FROM tableName
UNION ALL
SELECT 3, 3, SUM(`17`)
FROM tableName
UNION ALL
SELECT 3, 4, SUM(`18`)
FROM tableName
UNION ALL
SELECT 3, 5, SUM(`19`)
FROM tableName
UNION ALL
SELECT 3, 6, SUM(`20`)
FROM tableName
UNION ALL
SELECT 3, 7, SUM(`21`)
FROM tableName
UNION ALL
SELECT 4, 1, SUM(`22`)
FROM tableName
UNION ALL
SELECT 4, 2, SUM(`23`)
FROM tableName
UNION ALL
SELECT 4, 3, SUM(`24`)
FROM tableName
UNION ALL
SELECT 4, 4, SUM(`25`)
FROM tableName
UNION ALL
SELECT 4, 5, SUM(`26`)
FROM tableName
UNION ALL
SELECT 4, 6, SUM(`27`)
FROM tableName
UNION ALL
SELECT 4, 7, SUM(`28`)
FROM tableName

Answer 2

sum all rows values per column and after, change column for a date an DAYOFWEEK to group the values