1

I have to Write a query that uses a variable to list all transactions, sorted by date, performed by the customer with the ID 1. The variable should keep a running balance to show what the customer's new balance is after each transaction. The final output should give the date, amount of the transaction, and current balance after that transaction.

Table

+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| customer_id  | int(10) unsigned | YES  | MUL | NULL              |                             |
| last_created | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| amount       | decimal(6,2)     | NO   |     | NULL              |                             |
+--------------+------------------+------+-----+-------------------+-----------------------------+

Query

SELECT last_created, amount , sum(amount) as moneyspent FROM transactions where 
customer_id = 1   ORDER BY  last_created;
hue manny
  • 239
  • 2
  • 19

1 Answers1

1
SET @total = 0; 
SELECT last_transaction, amount ,@total := @total + amount AS runningtotal 
FROM sakila_payment 
ORDER BY last_transaction;