how to display running count of records in query 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.
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