title: How to group by on a column from a sub query
body: <p>I have  a tasks table with</p>
  
  <pre><code>tasks_id | start_time_id | end_time_id | start_date | end_date
  
  t1       | st_1          | et_1        | 20180903   | 20180903
  t2       | st_2          | et_2        | 20180903   | 20180903
  t3       | st_3          | et_3        | 20180903   | 20180903
  t4       | st_4          | et_4        | 20180903   | 20180903
  t5       | st_5          | et_5        | 20180903   | 20180903
  t6       | st_6          | et_6        | 20180903   | 20180904
  </code></pre>
  
  <p><code>start_time</code> and <code>end_time</code> are ids that join with <code>time_table</code> to get the military hours like </p>
  
  <pre><code>   time_id   | military_hour 
     st1       | 0
     st2       | 2
     st3       | 7
     st4       | 16
     st5       | 18
     st6       | 23
     et1       | 0
     et2       | 2
     et3       | 8
     et4       | 16
     et5       | 18
     et6       | 0  
  </code></pre>
  
  <p>I can join with <code>tasks</code> table with <code>time_table</code> <strong>twice</strong> if I want to know the military hours of both <code>start_time_id</code> and <code>end_time_id</code>.</p>
  
  <pre><code>SELECT        t1.tasks_id as task_inst_id,
                t1.start_time_id,
                t1.end_time_id,
                t1.end_date as end_date,
                t1.start_date as start_date
        FROM task_instances t1
        INNER JOIN time_table td1 
                ON t1.start_time_id = td1.time_id
        INNER JOIN time_table td2 
                ON t1.end_time_id = td2.time_id 
  </code></pre>
  
  <p><strong>I can divide the military hours in windows of 4 hours each so in a day there are 6 windows or military hour groups</strong> like</p>
  
  <pre><code>  (FLOOR(military_hour / 4)) AS military_hour_group
  </code></pre>
  
  <p>I want to know <code>no of all the tasks that either started or ended in those windows if I pass a particular day</code>.</p>
  
  <p>I tried</p>
  
  <pre><code>SELECT
  tq1.start_military_hour_group,
  tq1.end_date,
  COUNT(tq1.task_inst_id) as no_of_tasks
  FROM
  (
  SELECT        t1.tasks_id as task_inst_id,
                t1.start_time_id,
                t1.end_time_id,
                t1.end_date as end_date,
                (FLOOR(td1.military_hour / 4)*4) AS start_military_hour_group,
                (FLOOR(td2.military_hour / 4)*4) AS end_military_hour_group
        FROM task_instances t1
        INNER JOIN time_table td1 
                ON t1.start_time_id = td1.time_id
        INNER JOIN time_table td2 
                ON t1.end_time_id = td2.time_id 
  
        /* I don't know how to put the where condition */
  
        WHERE t1.end_date = '20180903':: int 
  )tq1
  GROUP BY tq1.start_military_hour_group,tq1.end_date
  ORDER BY tq1.end_date,tq1.start_military_hour_group;
  </code></pre>
  
  <p>I know I am missing something but what exactly?</p>
  
  <p><strong>I am joining twice so I guess it creates 2 rows for the same row from tasks table also should I take end_date or start_date?</strong></p>
  
  <p>Please explain.</p>
  
  <p>E.g. For the sample set</p>
  
  <pre><code>0 - 3 group - 2 tasks
  4-7 group - 1 task
  8-11 group - 0 
  12-15 group - 0
  16 - 19 group - 2 taks
  20-23 group - 1 task
  </code></pre>