Count Occurences in SQL

Question

I have the following data in a column;

0
1
1
0
0
1
0
0
1

I need to count the number of times the value of 1 occurs but if it occurs sequentially, it only counts as 1, my solution needs to be as follows; Occurences = 3

Answer 1

Hi，@Mark Jankowski

Welcome to Microsoft T-SQL Q&A Forum!

Please try this：

create table #t (Id int identity(1,1), Name char)  
insert into #t values  
('0'),  
('1'),  
('1'),  
('0'),  
('0'),  
('1'),  
('0'),  
('0'),  
('1')  
  
WITH cte AS (  
 SELECT Id, Name,   
 grp = CASE WHEN Name =1 AND prev=0 THEN 1 ELSE 0 END  
 FROM (SELECT *, prev = LAG(Name) OVER(ORDER BY id) FROM #t) s  
)  
SELECT Sum(GRP) as count  
FROM CTE  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Mark Jankowski ,

Please try the following solution.
It is counting continuous ranges of 1.
Amazingly enough, calculated column series is a constant for a continuous group of values for the column winn.

Here is what is coming after the cte, an intermediate data set.
Just compare two columns: winn vs. series.

+-----+------+------+--------+  
| seq | winn |  ns  | series |  
+-----+------+------+--------+  
|   1 |    0 | NULL |      0 |  
|   2 |    1 | 0    |      1 |  
|   3 |    1 | 1    |      1 |  
|   4 |    0 | 1    |      2 |  
|   5 |    0 | 0    |      2 |  
|   6 |    1 | 0    |      3 |  
|   7 |    0 | 1    |      4 |  
|   8 |    0 | 0    |      4 |  
|   9 |    1 | 0    |      5 |  
+-----+------+------+--------+  

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (seq INT IDENTITY(1,1) PRIMARY KEY, winn SMALLINT);  
INSERT INTO @tbl (winn) VALUES  
(0),(1),(1),(0),(0),(1),(0),(0),(1);  
-- DDL and sample data population, end  
  
;WITH cte AS  
(  
   SELECT *, SUM(IIF(winn <> ns, 1, 0)) OVER (ORDER BY seq) AS series  
   FROM (  
      SELECT series.*,  
            LAG(winn) OVER (ORDER BY seq) AS ns  
      FROM @tbl AS series  
   ) q  
)  
SELECT result = COUNT(DISTINCT series)   
FROM cte  
WHERE winn = 1;  

Output

+--------+  
| result |  
+--------+  
|      3 |  
+--------+  

Answer 3

create table test (id int identity primary key,n int)
insert into test (n)values
(0),
(1),
(1),
(0),
(0),
(1),
(0),
(0),
(1) 
;with mycte as (
select *,row_number() over(order by id)
-row_number() over(partition by n order by id) grp
from test
)
,mycte2 as (
select n,grp from mycte 
group by n,grp
having ( count(*)=2)
)

Select count(*) from mycte2

drop table test

Answer 4

1 occurs but if it occurs sequentially

In what for a sequence; data don't have any natural order as long as you define one in a ORDER by clause.

Answer 5

Hi MarkJankowski-4547

if it occurs sequentially, it only counts as 1. So you nedd to join the table to itself with the next row. You valide the condition with an "case" function and a simple addition gives the solution. I hope bring some help.

-- for create the test

create table toto 
(
    id int identity(1,1),
    valeur int
)
go

insert into toto values
 ('0'),
 ('1'),
 ('1'),
 ('0'),
 ('0'),
 ('1'),
 ('0'),
 ('0'),
 ('1')
 go

 -- solution

 select
    sum(calcul_table.calcul) as numbers_of_ones
 from
 (
     select 
        toto.id,
        toto.valeur,
        ISNULL(b.valeur, 0) as b_valeur,
        ISNULL((case
            when toto.valeur = 1 and b.valeur = 0 then 1
            else 0
        end), 0) as calcul
     from
        toto
        left join toto as b
        on toto.id = b.id + 1
) as calcul_table

Best Regards