Subquery and nested query in mysql

Question

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...

Can you share sample input data and expected output?

lemon
– lemon

2022-08-21 10:47:20 +00:00
Commented Aug 21, 2022 at 10:47 — lemon
– lemon, Commented Aug 21, 2022 at 10:47
You can look into GROUP BY and aggregate functions.

Christian Abila
– Christian Abila

2022-08-21 10:53:30 +00:00
Commented Aug 21, 2022 at 10:53 — Christian Abila
– Christian Abila, Commented Aug 21, 2022 at 10:53

Ozan Sen · Accepted Answer · 2022-08-21 16:40:28Z

1

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:

edited Aug 21, 2022 at 16:40

answered Aug 21, 2022 at 16:31

Ozan Sen

2,6152 gold badges6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IDK Over a year ago

I didn't know about CTE, interesting. As far as your query, is almost identical to mine. Thanks for taking the time!

Ozan Sen Over a year ago

Hi @IDK, Yes! Not a big difference. For me, CTE's are very important topic to learn in sql, together with window functions; because It gives you amazing flexibility to solve challenging problems. You can learn more about CTE's here: dev.mysql.com/doc/refman/8.0/en/with.html

IDK · Accepted Answer · 2022-08-21 15:06:04Z

0

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;

answered Aug 21, 2022 at 15:06

IDK

4656 silver badges26 bronze badges

Collectives™ on Stack Overflow

Subquery and nested query in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related