Accumulating values from 1 table and inserting to another one

Transact-SQL
1

SQL 2014.

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

http://shrani.si/f/2K/G7/2XRGaRSP/sqlhelp1.jpg
http://shrani.si/f/1J/OQ/2ZiA3sDk/sqlhelp2.png

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.

Thanks,
Klemen

2

Please read this and repost your question following proper netiquette.

3

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

WITH acc AS (
SELECT
	[account_id], SUM([kc_amount]) AS [Total]
FROM
	store_transactions
GROUP BY
	[account_id]
)

UPDATE TB_USER
SET [nKnightCash] = (SELECT [Total] FROM acc WHERE acc.[account_id] = [id])
WHERE [ID] IN (SELECT [account_id] FROM acc)
1 Like
4

Thanks mate,

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

FROM
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 !