tqsl - result from select query to operate as variable in subqueries?

For each user, I want to produce an aggregate result. However, I keep running into the problem of the 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]** 
       开发者_如何学Go     AND [type] = 'debit' )) 
     FROM transactions

How do I get the user from the select at the start to then operate as the variable user in the subqueries?

use CASE to simplify the summing:

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 further as:

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

The answers using subqueries for the summing activity, whilst still selecting user from the transactions table will perform the summation multiple times - once for every row in the transactions table - so if one user has 5 transactions, they're going to compute the sums 5 times (and show 5 result rows), which I assume you do not want.

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

try this:

 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 this:

 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

I like @Rubens answer, but...

Your data structure could use some work.

Actually storing the word 'credit' or 'debit' for each transaction, let alone having to evaluate it with a case statement in order to turn it into a usable value (1 or -1) is costly.

If your transaction table keeps track of quantity, then you might be able to use that instead of storing and evaluating 'credit' and 'debit'. Positive quantities (e.g., I bought 2 rakes, etc.); and Netagive quantities (e.g., -1 rake, which implies that you returned or got a credit for one of the rakes) can be summed without performing any case-statement evaluations.

Maybe

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

A variation on @Rubens Farias's solution:

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]