Group contiguous rows in PostgreSQL

Question

Given this table:

| i | x |
+---+---+
| 1 | A |
| 2 | A |
| 3 | B |
| 4 | B |
| 5 | B |
| 6 | B |
| 7 | A |
| 8 | A |
| 9 | A |

How can I group the rows that share the same letter (in column x) to get the following output?

| x | c |
+---+---+
| A | 2 |
| B | 4 |
| A | 3 |

Additionally, I'd like to output the amount of rows that have been grouped (shown in column c).

I tried the following SQL query:

SELECT x, count(*) c
FROM table_name
GROUP BY x

This query though groups all rows with the same value and that is not what I want, because the result doesn't match my desired one. Both A-groups are grouped together into one instead of keeping them separate.

| x | c |
+---+---+
| A | 5 |
| B | 4 |

Is there any column can represent the order?

D-Shih
– D-Shih

2018-11-10 21:51:42 +00:00
Commented Nov 10, 2018 at 21:51 — D-Shih
– D-Shih, Commented Nov 10, 2018 at 21:51

D-Shih · Accepted Answer · 2018-11-10 22:12:27Z

5

This is a Gaps and Islands problem

Islands and gaps appear in all sorts of sequences, be they row number columns where some rows have been removed or dates that occur in sequence (but some are missing).

You can try to make grp column for the gap group by number in a subquery, then you can group by on grp and X columns in the main query and get count

select X,COUNT(grp) c
from (
   SELECT *,  
          ROW_NUMBER() OVER(ORDER BY i) -  ROW_NUMBER() OVER(PARTITION BY X ORDER BY i) grp
   FROM table_name
) t1
group by grp,X

sqlfiddle

Result

x   c
=====
A   2
B   4
A   3

edited Nov 10, 2018 at 22:12

answered Nov 10, 2018 at 22:06

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D-Shih Over a year ago

@David No problem glad to help :)

Collectives™ on Stack Overflow

Group contiguous rows in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related