How to get the count rows for repeated non sequence values

Question

I have a table with below values.

Table Name: TestSeqeunce

-----------------
ID  |   Sno     |
-----------------
1   |    1      |   
2   |    1      |    
3   |    0      |    
4   |    0      |
5   |    0      |
6   |    1      |
7   |    0      |
8   |    1      |
9   |    1      |
10  |    0      |
-----------------

Output Explanation for Sno 1:

Sno --> 1,2 => 1
Sno --> 6   => 2
Sno --> 8,9 => 3

Output Explanation for Sno 0:

    Sno --> 3,4,5 => 1
    Sno --> 7   => 2
    Sno -->  => 3

Final Output :

  ---------
  |  Sno  |
  ----------
  |  3    |     --> 1 (Three times number 1 is occurred) 
  |  3    |     --> 0 (Three times number 0 is occurred)
  ---------

Can you hand off to some other app, process, script to do this processing? SQL is better for working with sets. You need to process an entire sequence to a quite different form. — Luke, Aug 03 '22 at 12:13
You may be able to count the number of times that a 1 is followed by a 0, and add one to the count if the final row is 1. Something like: `select count(*) from TestSequence as t1 join TestSequence as t2 on t1.ID + 1 = t2.ID where t1.Sno = 1 and t2.Sno = 0`. — Luke, Aug 03 '22 at 12:16
"Sno --> => 3" Is that a typo? Shouldn't that refer to ID 10? — SMor, Aug 03 '22 at 12:18
Sno --> 3 means 3 times 0 occurred. For example. consider my table row values is 1,0,0,1,0,1,0. Actually 4 zero count. But if i am having repeated row value means i don't want to count the zero. So, the Actual and required result is 3count. — Dhamodaran M, Aug 03 '22 at 12:26
Hi @DhamodaranM, can you share your best coding attempt at this problem? — lemon, Aug 03 '22 at 12:35
Your "Final Output" has two rows, but no way to determine the meaning of them nor to order them. Is that a problem? — HABO, Aug 03 '22 at 14:23

score 0 · Answer 1 · answered Aug 03 '22 at 13:21

This is a classic gaps-and-islands problem. In this case it's somewhat simplified, because you just want to know how many groups there.

SELECT
  t.Sno,
  COUNT(*)
FROM (
    SELECT *,
      PrevSno = LAG(t.Sno) OVER (ORDER BY t.ID)
    FROM YourTable t
) t
WHERE (t.Sno <> t.PrevSno OR t.PrevSno IS NULL)
GROUP BY
  t.Sno;

db<>fiddle

How to get the count rows for repeated non sequence values

1 Answers1