Qfyilyi

how to count how many um_no by different section and cumulative by hour with no specific function

original:

+-------+------+------+-----+

| um_no | nums | hour | day |

+-------+------+------+-----+

| mary  |    3 |    8 |   1 |

| john  |    6 |    8 |   1 |

| peter |    2 |    8 |   1 |

| jason |    1 |    8 |   1 |

| mary  |    5 |    9 |   1 |

| john  |    6 |    9 |   1 |

| peter |    6 |    9 |   1 |

| jason |    1 |    9 |   1 |

| mary  |    5 |   10 |   1 |

| john  |    4 |   10 |   1 |

| peter |    2 |   10 |   1 |

| jason |    4 |   10 |   1 |

+-------+------+------+-----+

want:

+-----+------+---------+----------+-----------+----------+

| day | hour | nums0_5 | nums5_10 | nums10_15 | nums15up |

+-----+------+---------+----------+-----------+----------+

|   1 |    8 |       3 |        1 |         0 |        0 |

|   1 |    9 |       1 |        2 |         1 |        0 |

|   1 |   10 |       0 |        2 |         1 |        1 |

+-----+------+---------+----------+-----------+----------+

You can try.. Use with suggestion by gordon which are really great..

select

  day,

  hour,

  count(*) filter (where sum_nums >= 0 and sum_nums <= 5) as nums0_5,

  count(*) filter (where sum_nums >= 5 and sum_nums <= 10) as nums5_10,

  count(*) filter (where sum_nums >= 10 and sum_nums <= 15) as nums10_15,

  count(*) filter (where sum_nums >= 15) as nums15up

from

  (select 

      *,

      sum(nums) over (partition by um_no, day order by hour) as sum_nums

   from 

      tbl) t

group by

  day,

  hour

You can see in here DEMO

Use conditional aggregation:

select day, hour,

       sum( case when nums >= 0 and nums < 5 then 1 else 0 end ) as nums_0_5,

       sum( case when nums >= 5 and nums < 10 then 1 else 0 end ) as nums_5_10,

       . . .

from t

group by day, hour;

In Postgres, the sums can be simplified to:

       count(*) filter (where nums >= 0 and nums < 5) as nums_0_5,

In MySQL:

       sum( nums >= 0 and nums < 5 ) as nums_0_5,

EDIT:

You can get the cumulative numbers using window functions and then aggregate:

select day, hour,

       sum( case when cume_nums >= 0 and cume_nums < 5 then 1 else 0 end ) as nums_0_5,

       sum( case when cume_nums >= 5 and cume_nums < 10 then 1 else 0 end ) as nums_5_10,

       . . .

from (select t.*,

             sum(nums) over (partition by um_no, date order by hour) as cume_nums

      from t

     ) t

group by day, hour;

select tt.day, tt.hour,

       sum(tt.nums_0_5) as nums_0_5 ,

       sum(tt.nums_5_10) as nums_5_10 ,

       sum(tt.nums_10_15) as nums_10_15, 

       sum(tt.nums15up) as nums15up

from

(select sum(case when nums >= 0 and nums < 5 then 1 else 0 end ) as nums_0_5,   

        sum( case when nums >= 5 and nums < 10 then 1 else 0 end ) as nums_5_10,

        sum( case when nums >= 10 and nums < 15 then 1 else 0 end ) as nums_10_15,

        sum( case when nums >= 15 then 1 else 0 end ) as nums15up,

        t.day,

        t.hour, 

        t.um_no

        from (select tbl.day, 

                     tbl.hour,

                     tbl.um_no,

                     tbl.nums,

                     Row_Number() over(partition by hour order by hour asc) as row_nums

              from tblTest tbl group by hour, um_no, nums, day) t

              group by t.day, t.hour, t.um_no

) tt group by  tt.hour, tt.day