Geek Answers Handbook

Tqsl - result of select query to work as a variable in subqueries?

For each user I want to create an aggregate result. However, I continue to run into the problem of a subquery returning more than 1 result.

SELECT **[user]**, ((SELECT SUM(amount) FROM transactions WHERE [user] = **[user]** AND [type] = 'credit' ) - (SELECT SUM(amount) FROM transactions WHERE [user] = **[user]** AND [type] = 'debit' )) FROM transactions

How do I get user from select at the beginning, then to work as user variable in subqueries?

+4
source share
6 answers

use CASE to simplify the summation:

 SELECT user,SUM(CASE type WHEN 'credit' THEN amount WHEN 'debit' THEN -amount END) from transactions GROUP BY user

If credit and debit are the only valid types, then it can be simplified as follows:

 CASE type WHEN 'credit' THEN amount ELSE -amount END

Answers using subqueries for summarizing activity, while selecting a user from the transaction table will sum several times - once for each row in the transaction table, so if one user has 5 transactions, they are going to calculate the sum 5 times (and show 5 lines of result) which I suppose you do not need.

+3
source 
 select username, SUM(amount * case when operation = 'credit' then 1 else -1 end) as balance from #transaction group by username
+2
source

try the following:

  SELECT user, (SELECT SUM(amount) FROM transactions WHERE [user] = t.User AND [type] = 'credit') - (SELECT SUM(amount) FROM transactions WHERE [user] = t.User AND [type] = 'debit' )) FROM transactions t

or that:

  SELECT user, Sum (Case type When 'credit' then Amount End) credit, Sum (Case type When 'debit' then Amount End) debit, Sum (Case type When 'credit' then Amount When 'debit' then -Amount End) NetAmount FROM transactions t Group By User
+1
source

I like @Rubens answer, but ...

Your data structure may use some work.

Actually saving the word “credit” or “debit” for each transaction, not to mention the need to evaluate it with a case-case in order to turn it into a useful value (1 or -1), is expensive.

If your transaction table tracks the quantity, then you can use this instead of storing and evaluating "credit" and "debit." Positive quantities (for example, I bought 2 rakes, etc.); and Netagive amounts (for example, -1 rake, which means that you returned or received a loan for one of the rakes) can be summed up without making any case-statement evaluations.

+1
source

Can

 SELECT t.user, ((SELECT SUM(amount) FROM transactions WHERE [user] = t.user AND [type] = 'credit' ) - (SELECT SUM(amount) FROM transactions WHERE [user] = t.user AND [type] = 'debit' )) FROM transactions t
0
source

Solution Option @Rubens Farias :

 SELECT t.[user], SUM(t.amount * x.factor) FROM transactions t INNER JOIN ( SELECT 'credit', 1 UNION ALL SELECT 'debit', -1 ) x (type, factor) ON t.type = x.type GROUP BY t.[user]
0
source

More articles:


All Articles