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 |
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';
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;
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
SELECT sub1, sub2, date in the second line? Later on you call day1_info.date. BTW, thank you!