How can I subtract two row's values within same column using sql query?
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 sub2FROM table t1 CROSS JOIN table t2WHERE 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 sub2FROM 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,
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