Subquery and nested query in mysql-CodePudding

I have a table with an operation and an amount columns. The opearation is an ENUM with two values: "in", "out". amount is just an INT.

I would like to sum the amounts "in" and subtract the sum of the amounts "out" in a single query.

I can extract a single value at a time:

SELECT SUM(amount) as total_in
FROM movements
WHERE operation like "in"

but I have no idea how to do both in one query...

CodePudding user response：

I've found a solution, here it is:

with deposits as (
     select sum(amount) as total_deposits
     from movements
     where operation like "deposit"),
    withdrawals as (
         select sum(amount) as total_withdrawals
         from movements
        where operation like "withdrawal")
select (withdrawals.total_withdrawals - deposits.total_deposits ) as net_profit
from deposits cross join withdrawals;

CodePudding user response：

Hello Please test this: (CTE-Common Table Expressions):

WITH cte1 AS (SELECT SUM(amount) AS total_in FROM movements WHERE operation = "in")
,cte2 AS (SELECT SUM(amount) AS total_out FROM movements WHERE operation = "out")
SELECT *, (total_in - total_out) as difference FROM cte1 JOIN cte2;

If we test it with some fake data: