CODEWITHSUNDEEP
mysqlsql
Ishara Kularatna
Ishara Kularatna

Reputation: 161

Get subtraction done through mysql

I have below mentioned table called myData1

ID   Value
1     150
2     120
3     100

I could get the last two values using below query:

SELECT value from myData1 order by ID desc limit 2;

I need to get the subtraction total of these two values (in this result set the result should be 100-120==> -20

Appreciate if someone can help to get this result

Upvotes: 1

Views: 44

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Approach 1

  • Use Correlated Subquery to get the first and second last value as two separate columns.
  • You can then use the result-set as Derived Table, to compute the difference.

Try (DB Fiddle DEMO #1):

SELECT dt.last_value - dt.second_last_value 
FROM 
(
  SELECT 
    t1.Value as last_value, 
    (SELECT t2.Value  
     FROM myData1 AS t2
     WHERE t2.ID < t1.ID 
     ORDER BY t2.ID DESC LIMIT 1) AS second_last_value
  FROM myData1 AS t1 
  ORDER BY t1.ID DESC LIMIT 1
) AS dt

Approach 2

  • Break into two different Select Queries; Use Limit with Offset. For last item, use a factor of 1. For second last, use the factor of -1.
  • Combine these results using UNION ALL into a Derived Table.
  • Eventually, sum the values using respective factors.

You can do the following (DB Fiddle DEMO #2):

SELECT SUM(dt.factor * dt.Value) 
FROM 
(
  (SELECT Value, 1 AS factor 
   FROM myData1 
   ORDER BY ID DESC LIMIT 0,1)

  UNION ALL

  (SELECT Value, -1 AS factor 
   FROM myData1 
   ORDER BY ID DESC LIMIT 1,1)
) AS dt

Upvotes: 1

Related Questions