How to group the same values which is in sequence order

Question

I'm trying to group data in sequence order. I have the following table:

id  num
-------
1   1
2   1
3   1
4   2
5   1
6   2
7   2
8   4
9   4
10  4

I need the SQL query to output the following:

num       count(num)
-------------------    
1          3    
2          1    
1          1    
2          2    
4          3 

Sample data:

select * into #temp 
from (
    select 1 as id, 1 as num union all
    select 2,  1  union all
    select 3,  1  union all
    select 4,  2  union all
    select 5,  1  union all
    select 6,  2  union all
    select 7,  2  union all
    select 8,  4  union all
    select 9,  4  union all
    select 10, 4 
) as abc
select * from #temp

select num, count(num) from #temp group by num

I need this :

num       count(num)    
-------------------    
1          3    
2          1    
1          1    
2          2    
4          3 

The actual output :

num    count(num)    
---------------------    
1       4    
2       3    
4       3

Answer 1

This is a gaps and islands problem. Here is one way to solve it using lag() and a cumulative sum():

select min(num) num, count(*) count_num
from (
    select t.*, sum(case when num = lag_num then 0 else 1 end) over(order by id) grp
    from (
        select t.*, lag(num) over(order by id) lag_num
        from #temp t
    ) t
) t
group by grp

Demo on DB Fiddlde:

num | count_num
--: | --------:
  1 |         3
  2 |         1
  1 |         1
  2 |         2
  3 |         3

Answer 2

Gaps and islands problems are fun because there are so many different ways to address them. Here is one approach that does not require aggregation -- although it does require more use of window functions.

This is possible because the only information you are requesting is the count. If the id has no gaps and is sequential:

select num,
      lead(id, 1, max_id + 1) over (order by id) - id
from (select t.*,
             lag(num) over (order by id) as prev_num,
             max(id) over () as max_id
      from temp t
     ) t
where prev_num is null or prev_num <> num
order by id;

Otherwise, you can generate such a sequence easily:

select num,
      lead(seqnum, 1, cnt + 1) over (order by id) - seqnum
from (select t.*,
             lag(num) over (order by id) as prev_num,
             row_number() over (order by id) as seqnum,
             count(*) over () as cnt
      from temp t
     ) t
where prev_num is null or prev_num <> num
order by id;

Here is a db<>fiddle.

Answer 3

Another Approach can be using row_number

select num, count(*) 
       from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by num order by id)
             ) as grp
             from #temp t
            ) t
group by grp, num;

DBFIDDLE