sql:count how many um_no by different section and cumulative by hour

Question

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 |
+-----+------+---------+----------+-----------+----------+

@dwir182 postgresql;preferably with no specific functions..thanks — Leon, Nov 22 '18 at 03:57
@dwir182 nums is the number of cases handled by certain people at certain hour — Leon, Nov 22 '18 at 04:18
@dwir182 before 8:00,there are 3 people handle case 0-5,and one person handle case 5-10.Cumulatively,there are 1 person handle total case 0-5,2 person handle taotal case 5-10,and 1 person handle total case10-15 — Leon, Nov 22 '18 at 04:32

score 2 · Answer 1 · answered Nov 22 '18 at 04:38

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

Gordon Linoff · Answer 2 · 2018-11-22T04:22:18.647

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;

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table). — Tim Biegeleisen, Nov 22 '18 at 04:17

Md. Mehedi Hassan · Answer 3 · 2018-11-22T06:28:46.080

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

sql:count how many um_no by different section and cumulative by hour

3 Answers3