Search code examples
mysqlinner-join

LTV Calculator MySQL

I have two tables let's call these 'transactions' and 'users'. The transactions table has an email address and the 'users' tables have an email addresses as well - this is the connecting primary key.

What I'd like to do is craft a query that will 'add' transactions from the 'transactions' table wherever an email address matches by adding the transaction_value amount to a field called 'LTV'

I.e. in pseudo-code Update users set LTV = LTV + (transactions value from transactions table) where (email on 'transactions' ) = (email on users).

what would be the syntax in MYSQL that perhaps uses an inner join?

Here is a Select Query that works to identify the users. 

SELECT
Transactions.`Customer_Email`,
users.Email,
Transactions.Price,
users.`Name`
FROM
Transactions
INNER JOIN users ON Transactions.Customer_Email = users.Email

How would you adapt this query to 'add' i.e. increase the LTV column (lifetime value - not average) - the sum total of the customers' sales with us so as a row is processed if there is an additional transaction in the 'Price' column it increases the value?

Solution 
  • UPDATE users u SET LTV = ( SELECT SUM(t.Price) FROM transactions t WHERE t.Customer_Email=u.Email )