how can I add a new column which counts the number of transactions?

userNfam1 0
Viewed 41

I searched for this answer but couldn't find anything. Hoping for a little help.

Transaction_ID User_ID Transaction_Date Amount DayOfTheWeek
Transaction_01 User_01 2021-09-06 532 Monday
Transaction_02 User_02 2021-09-05 631 Sunday
Transaction_03 User_03 2021-09-06 7214 Monday
Transaction_04 User_04 2021-09-08 131 Wednesday
Transaction_05 User_01 2021-09-08 13 Wednesday
Transaction_06 User_03 2021-09-09 72 Thursday
Transaction_07 User_05 2021-09-11 139 Saturday
Transaction_08 User_05 2021-09-13 214 Monday

Query Needed: I want to add new column called transaction order that would count no of occurrence , for example
i want column like this

Transaction_ID User_ID Transaction_Date Amount DayOfTheWeek TransactionOrder
Transaction_01 User_01 2021-09-06 532 Monday First
Transaction_02 User_02 2021-09-05 631 Sunday First
Transaction_03 User_03 2021-09-06 7214 Monday First
Transaction_04 User_04 2021-09-08 131 Wednesday First
Transaction_05 User_01 2021-09-08 13 Wednesday Second
Transaction_06 User_03 2021-09-09 72 Thursday Second
Transaction_07 User_05 2021-09-11 139 Saturday First
Transaction_08 User_05 2021-09-13 214 Monday Second
mysql sql
1 Answers

@Akina has already gave you the hint. Try this code:

SELECT *,
(CASE ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Transaction_Date)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END) AS TransactionOrder
FROM Transactions;

If you test it, It returns:

GHF

Ozan Sen0
Related