How to Sum multiple Column with multiple value

I am looking for a solution to the following:

  1. Go in to the users table and find a user who has listed items on the site. In this users table, there is no column about auctions. Instead, it is connected to an accounts table with a key (in accounts, this column is called user)

  2. From these IDs (users which have listed items for auction), I need to find their account balance. This is also in the accounts table. The balance is contained in a column called operation_amount. I also have another column called operation_type which describes whether a user has a positive or negative balance. For example, if operation_type = 1, he has a negative balance, while if operation_type = 2, he has a positive balance.

Now I have another table called tmpinvoice where there is a column called amount. This shows how much in fees a user needs to pay to the site administrators.

Given this, I need to calculate how much he must pay in total. For example, if a user has a $200 balance, I need to check whether it’s negative or positive based on the operation_type.

So i have querry where is Do this only for record

SELECT					AS id_user,	

	CASE ac.operation_type
	WHEN 1 THEN ac.operation_amount - tm.amount
	WHEN 2 THEN ac.operation_amount + tm.amount
		ELSE 'N/A'

		END				AS `fee`	

FROM auctionbg_search.accounts			AS ac	
	LEFT JOIN auctionbg_search.users	AS u	ON TRUE
		AND = ac.user
	LEFT JOIN auctionbg_search.auctions	AS a 	ON TRUE
		AND =
	LEFT JOIN auctionbg_search.tmpinvoice	AS tm  ON TRUE	
		AND tm.amount = ac.operation_amount

Here what result im reciving

Yes but this calculate ‘fee’ only for 1 value in column , what if user have multiple values

like this user :

I need calculate total amount from operation_amount of that given user and remove tmpinvoice from that total amount ,

1 my friend told me

to use IF(SUM(ac.operation_amount) , IS NULL ,0,sum(ac.operation_amount) and to join 2 time accounts(table) for both case with + and -

join 1 time for + , 2 time for -

but i cant figure out how will looks :slight_smile: