Sql-server – how to display running count of records in query

countgroup bysql-server-2012

I am working on SQL SERVER 2012, and I have been trying to figure out how to include a running count of records in a group by statement. For example, the query would look something like:

SELECT CUSTOMER_ID, TRANSACTION_ID, COUNT
FROM TRANSACTIONS
GROUP BY CUSTOMER_ID, TRANSACTION_ID
ORDER BY CUSTOMER_ID, TRANSACTION_ID

And the output would look something like:

10 100 1

10 108 2

10 125 3

10 140 4

12 102 1

12 103 2

12 138 3

15 135 1

I appreciate any advice on how to query this information.

Best Answer

You'll want to use a window function such as ROW_NUMBER() for this one like so:

SELECT CUSTOMER_ID, TRANSACTION_ID, ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY TRANSACTION_ID) AS [Count] -- Running incremental count of rows / TRANSACTION_ID per CUSTOMER_ID
FROM TRANSACTIONS 
GROUP BY CUSTOMER_ID, TRANSACTION_ID 
ORDER BY CUSTOMER_ID, TRANSACTION_ID
Related Question