CODEWITHSUNDEEP
sqlcountdistinct
user5410996
user5410996

Reputation: 25

Running total of rows by ID

I have a list of IDs, transactions, and the date of those transactions. I want to create a count of each transaction within each ID.

The starting table I have is looks something like this:

id  trxn_dt trxn_amt

1   10/31/2014  58

1   11/9/2014   34

1   12/10/2014  12

2   7/8/2014    78

2   11/20/2014  99

3   1/5/2014    120

4   2/17/2014   588

4   2/18/2014   8

4   3/9/2014    65

4   4/25/2014   74

and I want the end result to look something like this:

id  trxn_dt trxn_amt trxn_count

1   10/31/2014  58  1

1   11/9/2014   34  2

1   12/10/2014  12  3

2   7/8/2014    78  1

2   11/20/2014  99  2

3   1/5/2014    120 1

4   2/17/2014   588 1

4   2/18/2014   8   2

4   3/9/2014    65  3

4   4/25/2014   74  4

Count(distinct(id)) would only give me the overall number of distinct IDs and not a running total by each ID that restarts at each new ID.

Thank you!

Upvotes: 1

Views: 95

Answers (2)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

In SQL-Server you can use ROW_NUMBER in following:

SELECT id,
       trxn_dt,
       trxn_amt,
       ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id, trxn_dt) AS trxn_count
FROM   StarningTable

In MySQL you can do in following:

SELECT 
  t.id,
  t.trxn_dt,
  t.trxn_amt, 
  @cur:= IF(id=@id, @cur+1, 1) AS RowNumber, 
  @id := id 
FROM 
  StarningTable t 
CROSS JOIN 
    (SELECT @id:=(SELECT MIN(id) FROM StarningTable t), @cur:=0) AS init 
ORDER BY 
  t.id

Upvotes: 0

mohan111
mohan111

Reputation: 8865

using Row_number we can achieve this 

    Select *,
ROW_NUMBER()OVER(PARTITION BY id ORDER BY (SELECT NULL))trxn_count
 from Transactions

Upvotes: 0

Related Questions