CODEWITHSUNDEEP
sql
duroy
duroy

Reputation: 11

Subquery with two where clause

I have the table testing(name,money) where I have to return a result where all of the money that is positive is total into deposit and the negative amounts will be withdraw. With the query below it says :"Subquery is only allowed to return a single column."

SELECT name, SUM(money) AS "deposit"
FROM testing
where money > 0 in
    (SELECT name, SUM(abs(money)) AS "withdraw"
    FROM testing
    where money < 0 )
GROUP BY name;

p.s: i tried without the "In" and it's not working too

Upvotes: 0

Views: 88

Answers (2)

Zulfiqar Ali
Zulfiqar Ali

Reputation: 123

You can use the following SQL statement

SELECT name, SUM(money) AS "deposit"
FROM testing
where money > 0
AND money in
(
    SELECT SUM(abs(money)) AS "withdraw"
    FROM testing
    where money < 0 
)
GROUP BY name;

Let me know if it works! :)

Upvotes: -1

juergen d
juergen d

Reputation: 204746

SELECT name, 
       SUM(case when money > 0 then money else 0 end) AS deposit,
       SUM(case when money < 0 then money else 0 end) AS withdraw
FROM testing
GROUP BY name;

Upvotes: 2

Related Questions