How can I subtract two row's values within same column using sql query?

Question

I want to display the subtraction of two values from two different rows using a SQL query.

This is the table structure:

------------------------------------
id | name | sub1 | sub2 | date
------------------------------------
1  | ABC  | 50   | 75   | 2014-11-07
2  | PQR  | 60   | 80   | 2014-11-08

I want to subtract date 2014-11-08 subject marks from date 2014-11-07.

Output should be like as

| sub1  | sub2 |
 ---------------
|   10  |   5  |

score 22 · Accepted Answer · edited Nov 08 '14 at 13:06

22

You can use a join to get the rows and then subtract the values:

SELECT(t2.sub1 - t1.sub1) AS sub1, (t2.sub2 - t1.sub2) AS sub2
FROM table t1 CROSS JOIN
     table t2
WHERE t1.date = '2014-11-08' AND t2.id = '2014-11-07';

edited Nov 08 '14 at 13:06

Hardik Parmar

1,053
3
15
39

answered Nov 08 '14 at 12:31

Gordon Linoff

1,242,037
58
646
786

It seems cross joining is not the best answer: https://stackoverflow.com/questions/670980/performance-of-cross-join-with-where-clause-compared-to-inner-join – Ash Jun 22 '23 at 20:34

score 10 · Answer 2 · answered Nov 08 '14 at 17:14

I feel like you're leaving out an important part of your actual needs where you'll probably want to group by some specific field and return corresponding values, so the answer will be kind of limited. You can double reference the table like the example above, but it's usually much better if you can somehow only reference the table only once and remove the need for index lookups, bookmark lookups, etc. You can usually use simple aggregates or windowed aggregates to accomplish this.

SELECT
  MAX(sub1) - MIN(sub1) AS sub1, 
  MAX(sub2) - MIN(sub2) AS sub2
FROM
  dbo.someTable;

http://sqlfiddle.com/#!6/75ccc/2

score 7 · Answer 3 · answered Jul 28 '16 at 00:22

Cross joins can be difficult to work with because they relate data in ways that are usually unintuitive. Here's how I would do it instead, with the simple, default, INNER JOIN:

WITH day1_info AS
    (SELECT sub1, sub2
     FROM mytable)
SELECT
    day2_info.sub1 - day1_info.sub1 AS sub1_difference,
    day2_info.sub2 - day1_info.sub2 AS sub2_difference,
FROM
    mytable AS day2_info JOIN day1_info
        ON day1_info.date = '2014-11-07'
        AND day2_info.date = '2014-11-08'

If you'd like to do this for multiple sets of dates, you can do that too. Just change the JOIN statement slightly. (Note that in this case, you may also want to SELECT one of the dates as well, so that you know which time period each result applies to.)

WITH day1_info AS
    (SELECT sub1, sub2
     FROM mytable)
SELECT
    day2_info.date,
    day2_info.sub1 - day1_info.sub1 AS sub1_difference,
    day2_info.sub2 - day1_info.sub2 AS sub2_difference,
FROM
    mytable AS day2_info JOIN day1_info
        ON (day1_info.date::timestamp + '1 day') = day2_info.date::timestamp

Does it work without `SELECT sub1, sub2, date` in the second line? Later on you call `day1_info.date`. BTW, thank you! — aerijman, Oct 01 '21 at 01:00

How can I subtract two row's values within same column using sql query?

3 Answers3

Linked