0

I have the following table:

ID   Value
1     X
1     Y
1     X
1     X
1     X
1     Y
1     X
2     X
2     Y
2     X
2     Z
2     Y
2     X

I need to count the number of non-Consecutive times X appears per ID. So My output should be: 

ID    COUNT
1      3
2      3

Using Select Count(*), ID From my_table where value = 'X' calculates every occurrence of the value and not Non-Consecutive values only.

How do I solve this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
user10
  • 187
  • 2
  • 11
  • 1
    Not an answer to your question, but something you may need to understand before you ask a question like this: https://stackoverflow.com/a/20050403/5552667 – ZLK Apr 10 '19 at 23:24
  • Thanks ZLK. So now even if I add an ORDER BY ID to my query, it still returns counts all values for X instead of counting only non-consecutive ones. How do I get past this. – user10 Apr 10 '19 at 23:37
  • 1
    Sorry if it wasn't clear, but the concept of "consecutive" and "non-consecutive" here is incorrect. There's no explicit order to the letters. The order you're seeing by default _can_ change for a variety of reasons. You _could_ try doing something like `SELECT ID, COUNT(DISTINCT RN) FROM (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) - ROW_NUMBER() OVER (PARTITION BY ID, Value ORDER BY (SELECT NULL)) FROM my_table) AS T GROUP BY ID;` but whether this returns an accurate result or not, it may not produce an accurate result in the future. – ZLK Apr 10 '19 at 23:47

2 Answers2

1

First I have added an identity column in the table, that will create a Row number. Then I have created an second table and Make RN - 1 to get the next rows and make a join. Then lastly I have added condition for the Count value.

DECLARE @tbl TABLE
(
    RN int IDENTITY(1,1),
    Id int,
    Value varchar(10)
)

INSERT INTO @tbl
Values (1,'X'),(1,'Y'),(1,'X'),(1,'X'),(1,'X'),(1,'Y'),(1,'X'),(2,'X'),(2,'Y'),(2,'X'),(2,'Z'),(2,'Y'),(2,'X')

SELECT A.Id1, SUM(A.XCount) AS Count
From(
SELECT RN1 =  t.RN, Id1 = t.Id, Value1 = t.Value, RN2 = t2.RN, Id2 = t2.Id, Value2 = t2.[Value], 
XCount = CASE WHEN t.[Value] = 'X' AND (t.[Value] != t2.[Value] OR t.[Id] != t2.[Id] OR t2.[Value] IS NULL)  THEN 1 ELSE 0 END 
    FROM @tbl t
    Left JOIN (SELECT RN = RN - 1 , Id, Value FROM @tbl ) as t2 ON t.RN = t2.RN
) A
GROUP BY A.Id1
Hasan Mahmood
  • 978
  • 7
  • 10
1

You can use SQL server's lag function with recent versions to compare values in the previous row. The CTE below constructs a table containing id, value, previousId, previousValue and the second part counts occurrences where value contains X and the previous occurrence is different:

with cte as (
    select id, value,
    lag(id, 1, null) over (order by id, orderId) as previousId, 
    lag(value, 1, null) over (order by id, orderId) as previousValue from my_table
)
select id, count(*)
from cte
where value = 'X' and (id <> previousId or previousValue <> 'X' or previousValue is null)
group by id
J.R.
  • 1,880
  • 8
  • 16