SQLTeam.com | Weblogs | Forums

Accumulating values from 1 table and inserting to another one



SQL 2014.

Working with 2 tables here. I will provide image links as to which 2 so you can imagine easier.


So pretty much store_transaction table logs all payments. And virtual points acccumaltion goes into table TB_USER.
Collumn "kc_amount" is what is important in store_Transaction.
Now thing here is first it has to make a SUM of all payments per each account_id. For example user can make several payments and it will log each individual in the store_transaction table. So first a SUM has to be made for each ID per how many payments they have made total and it has to accumulate totat "kc_amount" per id. So if user with account_id=2 made 3 payments per 3€ (500 kc_amount). It will be 3 rows in the table stating 500 KC under kc_amount collumn and all 3 rows will have account_id 2. So the query should accumulate total kc_amount per 1 account_id in store_Transactions.
Then accumulated amount of kc_amount has to be updated under TB_USER nKnightCash collumn (accumulated kc_amout value should equal nKnightCash value in TB_USER table)

I need the query because I will set all nKnightCash values in TB_USER table to 0 and I will restore all points as to how payments were made getting all necesary information from store_transaction table.

"account_id" in store_transaction table matches "id" in TB_USER table (its linking the user accounts)

So how would query look like to accumulate it for all IDs at the same time and simultaneously update the accumulated kc_amount from store_transaction into nKnightCash in TB_USER.

I hope I explained it clear enough.



Please read this and repost your question following proper netiquette.


Assuming every account_id in store_transactions exists in TB_USER, you could try something like this:

WITH acc AS (
	[account_id], SUM([kc_amount]) AS [Total]

SET [nKnightCash] = (SELECT [Total] FROM acc WHERE acc.[account_id] = [id])
WHERE [ID] IN (SELECT [account_id] FROM acc)


Thanks mate,

it worked as a charm only thing I added is 1 more check

store_transactions where payment_status='approved'

because it logs initialized statuses aswell not only approved ones. So with that check it works like a charm.

And before I execute entire query I ofcrouse set nKnightCash=0 for all TB_USER accounts.

Much appreciated !