sql sequences count

Question

I have a table as follows

userid    answer

 1         true

 1         true

 1         false

 1         true

 1         true

 1         true  

 2         true

 1         true

I want to get the latest count of true sequence per user

so that I will get

userid   count

1         4
2         1

please help

Answer 1

SELECT userid, count(*) as count
FROM Table
WHERE answer = 'true'
GROUP BY userid

Answer 2

How about using ranking functions?

WITH Answers AS 
( 
  SELECT *
  FROM (VALUES
    (1, 1, 't'),
    (2, 1, 't'),
    (3, 1, 'f'),
    (4, 1, 't'),
    (5, 1, 't'),
    (6, 1, 't'),
    (7, 2, 't'),
    (8, 1, 't')
  ) AS tbl(xxxid, userid, answer)
),
A AS
(
   SELECT Answers.*, ROW_COUNT() OVER(PARTITION BY userid ORDER BY xxxid) AS RN1
   FROM Answers
),
B AS
(
   SELECT A.xxxid, A.userid,
          A.RN1 - ROW_COUNT() OVER(PARTITION BY A.userid ORDER BY A.xxxid) AS GF
   FROM A
   WHERE A.answer = 't'
),
C AS
(
   SELECT B.*,
          COUNT() OVER(PARTITION BY B.userid, B.GF) AS CNT,
          MAX(B.xxxid) OVER(PARTITION BY B.userid, B.GF) AS MAXID,
          MAX(B.GF) OVER(PARTITION BY B.userid) AS MAXGF
   FROM B
)
SELECT userid, CNT
FROM C
WHERE C.MAXGF = C.GF AND C.MAXID = C.xxxid

Haven't actually tested it so I'm biting my lip a bit.

Answer 3

Try this:

create table t
(
i int,
userid int, 
answer varchar(1)
);

create table u
(
userid int
);


insert into u values(1),(2);

insert into t values
(1, 1, 't'),
(2, 1, 't'),
(3, 1, 'f'),
(4, 1, 't'),
(5, 1, 't'),
(6, 1, 't'),
(7, 2, 't'),
(8, 1, 't');





with user_latest_true
as
(
    select userid, max(i) as latest 
    from t 
    where answer = 'f'
    group by userid

    union

    select u.userid, 0 as latest 
    from u
    where userid not in (select userid from t where t.answer = 'f')
)
select t.userid, count(ult.userid) x 
from user_latest_true ult 
left join t on t.userid = ult.userid and t.i > ult.latest 
group by t.userid;

Answer 4

WITH Answers
AS
(
SELECT 1 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 2 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 3 AS xxxid , 1 AS userid, 'false' AS answer UNION ALL
SELECT 4 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 5 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 6 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 7 AS xxxid , 2 AS userid, 'true' AS answer UNION ALL
SELECT 8 AS xxxid , 1 AS userid, 'true' AS answer )

SELECT   userid,
         COUNT(*) AS [COUNT]
FROM     Answers A
WHERE    NOT EXISTS
         (SELECT *
         FROM    Answers a2
         WHERE   answer    = 'false' /*change this to 0 if using bit datatype*/
         AND     a2.userid = a.userid
         )
OR       xxxid >
         (SELECT MAX(xxxid)
         FROM    Answers a2
         WHERE   answer    = 'false' /*change this to 0 if using bit datatype*/
         AND     a2.userid = a.userid
         )
GROUP BY userid

Answer 5

DECLARE @userID INT
DECLARE @answer BIT
DECLARE @answerCount INT

DECLARE  @AnswerCountTable AS TABLE
(
    userID  int,
    answer  int
)
-- Declare a cursor that will be used to search your table (userID, answer)
DECLARE myCursor CURSOR
    FOR SELECT * FROM YourTable
OPEN myCursor 
WHILE @@FETCH_STATUS = 0
BEGIN  
--Get The current userID and answer in variables 
    FETCH NEXT FROM myCursor INTO @userID, @answer;
    IF @answer = true 
        BEGIN
        UPDATE @AnswerCountTable 
            SET answer = answer + 1
            WHERE userID = @userID
        END
    ELSE
    BEGIN
        UPDATE @AnswerCountTable 
            SET answer = 0
            WHERE userID = @userID
    END
END

--Close the cursor
Deallocate myCursor

--Return the result     
SELECT * 
FROM @AnswerCountTable

Answer 6

Something like...

SELECT userid, 
       sum(case when answer='true' then 1 else 0 end) 
FROM   {tablename} 
GROUP BY userid