question

MarkJankowski-4547 avatar image
0 Votes"
MarkJankowski-4547 asked BertZhoumsft-7490 commented

Count Occurences in SQL

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

sql-server-transact-sqlsql-server-reporting-services
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

It is worth pointing out that all solutions presume that there is a column to define the order of the rows. This is an absolute requirement, since a table by definition is an unordered set and does not have inherent order by itself.

0 Votes 0 ·

@MarkJankowski-4547,

Did you have a chance to try the proposed solutions?
If any of them is working for you, please don't forget to accept it as Answer.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @MarkJankowski-4547,

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 |
 +--------+
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
2 Votes"
BertZhoumsft-7490 answered BertZhoumsft-7490 commented

Hi,@MarkJankowski-4547

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

205643-image.png


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.




image.png (989 B)
image.png (2.3 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried all the solutions but prefer this and right now I have it all set up using a stored procedure and outputting the value to a sql table. I was able to link it to the actual data that changes each day. I do have a question though, I have looked at the Microsoft docs and cannot seem to find the format for the small “s” …is the “s” naming the cte result? It’s the small “s” right after FROM #t)

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

1 Vote 1 ·

No, the s defines an alias for the derived table (SELECT *, prev = LAG(Name) OVER(ORDER BY id) FROM #t). When you have FROM (SELECT ...), this is known as a derived table, and you must define an alias for this table, even if you don't use it in the rest of the query. Remove the s and you get a syntax error.


2 Votes 2 ·

could I also apply the same solution to this type of problem?

I have the following data set;

Datetime Phase
5/1/2022 02:02:31 1
5/1/2022 02:12:35 0
5/7/2022 07:02:38 1
5/7/2022 07:05:31 0
5/8/2022 02:22:51 1
5/8/2022 02:32:35 0
5/9/2022 11:02:38 1
5/9/2022 11:05:31 0

I would like a query to create the following result;

Month Day Start End Time
5 1 02:02:31 02:12:35 00:10:04
5 1 07:02:38 07:12:35 00:09:57
5 7 07:15:38 07:19:35 00:03:57
5 8 02:32:35 02:42:15 00:09:40
5 9 11:02:38 11:05:31 00:02:53

                                     Total :00:36:31



0 Votes 0 ·
Show more comments
JingyangLi avatar image
0 Votes"
JingyangLi answered
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EricMILA-7882 avatar image
0 Votes"
EricMILA-7882 answered EricMILA-7882 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hm, this is the solution you would use on SQL 2008 and earlier when we did not have LEAD/LAG. But for SQL 2012 and later, this is clearly an inferior choice.

1 Vote 1 ·

But this query run faster with a lot data Android it's easy to understand

0 Votes 0 ·