Myujth

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 |

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