Find overdrawn accounts in SQL

Answer 1

Corrected working version:

Select innersql.UserName from (
    select 
          d.username,
          SUM(d.Amount) as depositamount,
          SUM(w.WithDraw)as withdrawamount 
    from deposit d
    inner join withdraw w
    on d.UserName = w.UserName
    group by d.UserName
     ) as innersql where depositamount > withdrawamount

Answer 2

Try this:

SELECT T1.UserName
FROM
(
    SELECT UserName, SUM(Amount) AS Amount
    FROM WithDraw
    GROUP BY UserName
) T1
LEFT JOIN
(
    SELECT UserName, SUM(Amount) AS Amount
    FROM Deposit
    GROUP BY UserName
) T2
ON T1.UserName = T2.UserName
WHERE T1.Amount > COALESCE(T2.Amount, 0)

The COALESCE is so that it also works if there are users that have withdrawn but never deposited.

Answer 3

An alternative method:

SELECT UserName, SUM(Amount) AS Amount
FROM (
    SELECT UserName, Amount
    FROM Deposit
    UNION ALL
    SELECT UserName, -Amount
    FROM WithDraw
) AS U (UserName, Amount)
GROUP BY UserName
HAVING SUM(Amount) < 0