CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
Zeeshan Saleem
Zeeshan Saleem

Reputation: 149

Count number of occurrences in a bit column in sql

How to count the change in a sql server column like I have Ignition value Ignition 1 1 0 1 1 1 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 1

I want to count change only it is from 0 to 1 to make occurrence 1. It can also be from 1 to 0 for the occurrence to be 1.

Upvotes: 2

Views: 605

Answers (4)

roman
roman

Reputation: 117636

Simplest and shortest way for SQL server 2008 I know is:

with cte as (
    select
        row_number() over(partition by Ignition order by Id) as rn1,
        row_number() over(order by Id) as rn2
    from Table1
)
select count(distinct rn2 - rn1) - 1
from cte

Or, as @MartinSmith pointed out:

with cte as (
    select
        row_number() over(order by Ignition, Id) as rn1,
        row_number() over(order by Id) as rn2
    from Table1
), cte2 as (
    select distinct Ignition, rn2 - rn1
    from cte
)
select count(*) - 1
from cte2

for SQL Server 2012 you can use lag() (or lead()) function:

;with cte as (
    select
        lag(Ignition) over(order by Id) as prev,
        Ignition as cur
    from Table1
)
select count(case when cur <> prev then 1 end)
from cte;

sql fiddle demo

Upvotes: -2

Don
Don

Reputation: 9661

Not sure if you want a solution that works in both 2008 and 2012 as you have both tags, but in 2012 (doesn't work in 2008) we did get LAG() and LEAD() so a SUM() of [Change] in the query below will do it for 2012. You'll have to decide how to handle the first value (which obviously doesn't have a previous value), current state it counts as a change.

SELECT [Id]
     , [Ignition]
     , LAG([Ignition]) OVER(ORDER BY [Id]) [Previous]
     , CASE WHEN LAG([Ignition]) OVER(ORDER BY [Id]) = [Ignition] THEN 0 ELSE 1 END [Change]
  FROM [dbo].[Table]
 ORDER BY Id;

For 2008 a self-join should produce the same result.

SELECT [T1].[Id]
     , [T1].[Ignition]
     , [T2].[Ignition] [Previous]
     , CASE WHEN [T1].[Ignition] = [T2].[Ignition] THEN 0 ELSE 1 END [Change]
  FROM [dbo].[Table] [T1]
  LEFT JOIN [dbo].[Table] [T2] ON [T1].[Id] = ([T2].[Id] + 1)
 ORDER BY [T1].[Id];

Upvotes: 1

gvee
gvee

Reputation: 17171

Step 1: use the Row_Number() function to provide a complete (un-broken) sequence of numbers, according to our order

SELECT ignition
     , id
     , Row_Number() OVER (ORDER BY id ASC) As row_num
FROM   your_table

Step 4: Make this a Common-Table Expression (CTE) so we can refer to the derived row_num column

; WITH cte AS (
  SELECT ignition
       , id
       , Row_Number() OVER (ORDER BY id ASC) As row_num
  FROM   your_table
)
SELECT ignition
     , id
     , row_num
FROM   cte

Step 3: join this table back to itself matching on the next/previous row

; WITH cte AS (
  SELECT ignition
       , id
       , Row_Number() OVER (ORDER BY id ASC) As row_num
  FROM   your_table
)
SELECT c1.ignition As c1_ignition
     , c2.ignition As c2_ignition
FROM   cte As c1
 LEFT
  JOIN cte As c2
    ON c2.row_num = c1.row_num + 1

Step 4: Filter the results to show those where the values aren't the same

; WITH cte AS (
  SELECT ignition
       , id
       , Row_Number() OVER (ORDER BY id ASC) As row_num
  FROM   your_table
)
SELECT c1.ignition As c1_ignition
     , c2.ignition As c2_ignition
FROM   cte As c1
 LEFT
  JOIN cte As c2
    ON c2.row_num = c1.row_num - 1
WHERE  c1.ignition <> c2.ignition

Step 5: ...

Step 6: profit!

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44356

declare @t table(id int identity(1,1), ignition bit)
insert @t values(1),(0),(1),(1)
declare @Ignition varchar(max) = ''

select @Ignition = @Ignition + cast(Ignition as char(1))
from @t order by id
select @ignition

select len(replace(replace(replace(@Ignition, '10', 'x') 
       + replace(@Ignition, '01', 'x'), 1, ''), 0, ''))

Result: 

2

Upvotes: 0

Related Questions