CODEWITHSUNDEEP
sql
Talk2Nit
Talk2Nit

Reputation: 1135

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

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  |

Upvotes: 12

Views: 148871

Answers (3)

Carolyn Conway
Carolyn Conway

Reputation: 1506

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

Upvotes: 8

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

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

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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';

Upvotes: 23

Related Questions