Skip to content
Advertisement

How to aggregate count of rows having the same value in a sequence?

I have a query that returns data in the following sample:

Output

I know how to group by ATM_ID and put the count in-front of each

Based on the sample data above, this will yield something like

However, I am interested in grouping on a different level. If a certain ATM_ID is duplicated in consecutive rows, the number of rows having the same ATM_ID in sequence should be included in the output, even if the same ATM_ID appears later after a different ATM_ID

Desired Output

Ignore the comments on the right, these are just for clarifications, not part of the output. Is that possible?

PS: The answer below by Syed Aladeen gives the output with the correct count, but with the wrong order. I create an SQL fiddle for convenience:

SQL Fiddle

Advertisement

Answer

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement