# Count Frequency of Consecutive Specified Value

• ### Question

• Hi all,

I'm currently working with a fairly large data set and am having some issue with a formula that will return the maximum frequency of a specified value that appears consecutively within a row.

For example, respondent ID might have data that look like (where the values under Q1 through Q10 are the response options they chose; 99 indicates a non-response/omit/skipped question)

ID Q1    Q2   Q3   Q4   Q5   Q6  Q7   Q8   Q9   Q10                  Max of 0      Max of 1    Max of 2     #of Skips

1    0      2      2     1     2     2     2     99    2     99                          1               1              3                  2

I've attached a workbook that will hopefully clarify a little bit more what I'm trying to accomplish.

Saturday, January 19, 2019 7:11 PM

• you can do like this

```;With CTE
AS
(
SELECT ID,Val,Grp,COUNT(*) AS Occurance
FROM
(
SELECT ID,Val,Col,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY REPLACE(Col,'PFi','') * 1) - ROW_NUMBER() OVER (PARTITION BY ID,Val ORDER BY REPLACE(Col,'PFi','') * 1) AS Grp
FROM Table
UNPIVOT(Val FOR Col IN ([PFi1],[PFi2],[PFi3],[PFi4],[PFi5],[PFi6],[PFi7],[PFi8],[PFi9],[PFi10]))u
)m
GROUP BY ID,Val,Grp
)

SELECT ID,
MAX(CASE WHEN Val = 0 THEN Occurance ELSE 0 END) AS [Max0inarow],
MAX(CASE WHEN Val = 1 THEN Occurance ELSE 0 END) AS [Max1inarow],
MAX(CASE WHEN Val = 2 THEN Occurance ELSE 0 END) AS [Max2inarow],
MAX(CASE WHEN Val = 3 THEN Occurance ELSE 0 END) AS [Max3inarow],
SUM(CASE WHEN Val = 99 THEN Occurance ELSE 0 END) AS [Skipped]
FROM CTE
GROUP BY ID```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Sunday, January 20, 2019 6:09 AM

### All replies

• you can do like this

```;With CTE
AS
(
SELECT ID,Val,Grp,COUNT(*) AS Occurance
FROM
(
SELECT ID,Val,Col,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY REPLACE(Col,'PFi','') * 1) - ROW_NUMBER() OVER (PARTITION BY ID,Val ORDER BY REPLACE(Col,'PFi','') * 1) AS Grp
FROM Table
UNPIVOT(Val FOR Col IN ([PFi1],[PFi2],[PFi3],[PFi4],[PFi5],[PFi6],[PFi7],[PFi8],[PFi9],[PFi10]))u
)m
GROUP BY ID,Val,Grp
)

SELECT ID,
MAX(CASE WHEN Val = 0 THEN Occurance ELSE 0 END) AS [Max0inarow],
MAX(CASE WHEN Val = 1 THEN Occurance ELSE 0 END) AS [Max1inarow],
MAX(CASE WHEN Val = 2 THEN Occurance ELSE 0 END) AS [Max2inarow],
MAX(CASE WHEN Val = 3 THEN Occurance ELSE 0 END) AS [Max3inarow],
SUM(CASE WHEN Val = 99 THEN Occurance ELSE 0 END) AS [Skipped]
FROM CTE
GROUP BY ID```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Sunday, January 20, 2019 6:09 AM
• you can do like this

```;With CTE
AS
(
SELECT ID,Val,Grp,COUNT(*) AS Occurance
FROM
(
SELECT ID,Val,Col,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY REPLACE(Col,'PFi','') * 1) - ROW_NUMBER() OVER (PARTITION BY ID,Val ORDER BY REPLACE(Col,'PFi','') * 1) AS Grp
FROM Table
UNPIVOT(Val FOR Col IN ([PFi1],[PFi2],[PFi3],[PFi4],[PFi5],[PFi6],[PFi7],[PFi8],[PFi9],[PFi10]))u
)m
GROUP BY ID,Val,Grp
)

SELECT ID,
MAX(CASE WHEN Val = 0 THEN Occurance ELSE 0 END) AS [Max0inarow],
MAX(CASE WHEN Val = 1 THEN Occurance ELSE 0 END) AS [Max1inarow],
MAX(CASE WHEN Val = 2 THEN Occurance ELSE 0 END) AS [Max2inarow],
MAX(CASE WHEN Val = 3 THEN Occurance ELSE 0 END) AS [Max3inarow],
SUM(CASE WHEN Val = 99 THEN Occurance ELSE 0 END) AS [Skipped]
FROM CTE
GROUP BY ID```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Thanks Visakh16! I'm not quite familiar with CTE. Would this solution be down in power query or powerpivot? Would you be able to post the workbook with the solution to get a better visual?

Thanks!

Sunday, January 20, 2019 3:33 PM
• Darko, any progress on this one?

Thanks!

Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)