Find profit or loss from same table for multiple user [duplicate]-CodePudding

Goal is to find the difference of total selling amount and total buying amount (sell_amount - buy_amount) So I am making 2 sql queries

SELECT user, SUM(amount) as buy_amount FROM orders WHERE type='buy' GROUP BY user


output table
─────┬─────────────
user | buy_amount
─────┼─────────────
1001 | 500
1002 | 200

SELECT user, SUM(amount) as sell_amount FROM orders WHERE type='sell' GROUP BY user
output table
─────┬─────────────
user | sell_amount
─────┼─────────────
1001 | 600
1002 | 150

To find the difference I have to make 2 query and calculate it with loops for all user Is it possible to get a column difference ?

Imagined output table =

─────┬─────────────
user | difference
─────┼─────────────
1001 | 100
1002 | -50

Sample dataset

CREATE TABLE `sample_order` (
  `id` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `type` varchar(5) NOT NULL,
  `amount` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `sample_order` (`id`, `user`, `type`, `amount`) VALUES
(1, 1001, 'buy', 500),
(2, 1002, 'buy', 200),
(3, 1001, 'sell', 600),
(4, 1002, 'sell', 150);

CodePudding user response：

WITH CTE(USER_IDD,OPER_SUMM, OPER_TYPE)
AS
(
   SELECT 1,100.00,'SELL' UNION ALL
   SELECT 1,200.00,'BUY' UNION ALL
   SELECT 2,500.00,'SELL'
)
SELECT X.USER_IDD,X.SELL_SUMM-X.BUY_SUMM AS DIFFERENCES  FROM
(
  SELECT C.USER_IDD,
  SUM
  (
    CASE
     WHEN C.OPER_TYPE='SELL' THEN C.OPER_SUMM
     ELSE 0
   END
 )AS SELL_SUMM,
SUM
(
  CASE
   WHEN C.OPER_TYPE='BUY' THEN C.OPER_SUMM
   ELSE 0
  END
)AS BUY_SUMM
   FROM CTE AS C
   GROUP BY C.USER_IDD
 )X

CodePudding user response：

Try:

SELECT user, SUM(case when type='buy' then amount end) as buy_amount,
             SUM(case when type='sell' then amount end) as sell_amount,
             ((SUM(case when type='buy' then amount end)) - (SUM(case when type='sell' then amount end))) as difference
FROM sample_order
GROUP BY user;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/50

CodePudding user response：

This should work:

SELECT b.user, SUM(b.amount)-SUM(s.amount) as difference FROM orders b left join orders s ON b.user = s.user WHERE b.type='buy' AND s.type='sell' GROUP BY user

CodePudding user response：

You can do conditional aggregation :

select user, sum(case when type = 'buy' then amt else -amt end) as difference
from sample_order
group by user;

CodePudding user response：

I think this is the simplest way

SELECT User, SUM(CASE WHEN type = 'buy' then amount * -1 ELSE amount  END) AS 'Difference' FROM sample_order GROUP BY User

If you want just a positive value for the difference you can also use ABS()

CodePudding user response：

Try this:

SELECT user, SUM(case when type='buy' then amount end) as buy_amount,
             SUM(case when type='sell' then amount end) as sell_amount,
             SUM(case when type='buy' then amount end) - SUM(case when type='sell' then amount end) as difference
FROM sample_order
GROUP BY user;

The SQL CASE Statement:

The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Reference: https://www.w3schools.com/sql/sql_case.asp

In your required query, there is no need for the ELSE part simply we are checking the conditions (type = "buy" and "sell") in order to find the total amount of buying and sells with SUM() function of SQL for each buy_amount and sell_amount.
and similarly deducting the same to find a difference between two of them which gives you the complete required result.

You can check an Example of same here: https://www.db-fiddle.com/f/5EmiBoLE8p3LnnTzeqSHw7/0