0

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.

1

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
4
  • Assuming transaction_id is unique in transactions, the group by clause can be removed – Lennart Feb 13 at 9:00
  • @Lennart True I would hope that's the case with their schema. :) But anything's possible when it's not my schema lol. – J.D. Feb 13 at 14:10
  • 1
    Absolutely, more of a note for the OP. Hard to tell without ddl in the question – Lennart Feb 13 at 14:12
  • Thanks for the added comments about being able to remove the group by clause. Yes, ID's are always unique in my schema. – Riggster Feb 15 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.