# Counting repeated occurences for a group of fields

I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg:

``````Time         person        Result
08:12:39     Sue           Tails
08:12 41     Dave          Tails
08:12:47     Sue           Tails
08:12:50     Dave          Tails
``````

Result should look like this

``````Dave     4
Sue      2
John     1
``````

The count for Dave would be 4 as between 08:12:42 and 08:12:49, 4 Heads options occur for Dave before Dave gets a Tails option.

• Why Dave is 4 and not 3? Dec 10 '14 at 9:39
• Or should Dave be 6? Dec 10 '14 at 9:42
• @Colin'tHart I think with "continuous" they mean consecutive. It looks like a gaps-and-islands problem. Dec 10 '14 at 9:43
• @Ypercube Probably. Dec 10 '14 at 9:44
• @ypercube The count for Dave would be 4 as between 08:12:42 and 08:12:49, 4 Heads options occur for Dave before Dave gets a Tails option Dec 10 '14 at 10:14

``````SELECT person, max(ct) AS max_ct
FROM  (
SELECT person, count(*) AS ct
FROM  (
SELECT person, result
, row_number() OVER (PARTITION BY person ORDER BY time)
- row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp
FROM   tablex
) sub1
GROUP BY person, result, grp
) sub2
GROUP  BY person
ORDER  BY max(ct) DESC, person;``````

SQL Fiddle.

Result exactly as desired.

After forming groups (`grp`) of consecutive pairs `(person, result)`, the count has to be per pair and group `(person, result, grp)`, not just per person and group `(person, grp)`. There can (and will) be one group with the same group number per person and result, those have to be counted separately.

Also, it's not efficient to use CTEs here. In Postgres, performance is generally superior for subqueries, since CTEs are optimization fences that always materialize the result. Use CTEs only where they are actually necessary.

• Correct, I missed that. Dec 10 '14 at 14:46
• @erwin brandstetter Yes. Thank you. Has resolvled the issue of the first value in results affecting query outcome. Dec 10 '14 at 14:49
• @Peter: It wasn't actually the first position of the row that affected the outcome. It just so happened that due to the additional row a second group with `grp = 2` was formed for the same `person` (one for each `result`) and both were lumped together by mistake. Dec 10 '14 at 14:53
• @erwin brandstetter Understand now better after your explanation. Is there any tool in sql that allows "step" function like you get in programming? This would help much when evaluating complex queries. Dec 10 '14 at 14:57
• @Peter: There are many tools. You would have to define more closely what a "step" should be. Remember that SQL is a declarative language (mostly), not a procedural language, before talking about "steps". If you need steps in the procedural sense I suggest PL/pgSQL. Example. Start a new question with the necessary details, if you still have a question to ask. Dec 10 '14 at 15:06