Clever Geek Handbook

How can I subtract two row values ​​in one column using a sql query?

I want to show subtraction

two values ​​from two

different rows using a query SQL

.

This is the structure of the table:

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

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

The output should look like

| sub1  | sub2 |
 ---------------
|   10  |   5  |
+3


source to share


4 answers


You can use a join to get strings 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';
+14


source


I feel like you are leaving an important part of your actual needs where you probably want to group by a specific field and return the appropriate values, so the answer is a bit limited. You can reference the table twice as in the example above, but it is usually much better if you can somehow only reference the table once and remove the need for index lookups, bookmark lookups, etc. You can usually use simple aggregates or window aggregates this.

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


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

+2


source


Cross-joins can be difficult to work with because they link data in a way that tends to be unintuitive. Here, as I did, with a 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 want to do this for multiple sets of dates, you can do that too. Just change the instruction JOIN

. (Note that in this case, you can also specify SELECT

one of the dates so that you know which time period each result belongs 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
+1


source


(access request) This is the structure of the table:

id | name | sub1

1 | ABC | 6.27%
2 | ABC | 7.47% 3 | PQR | 3.39%
4 | PQR | 2.21%

I want to subtract Sub1 ...

The output should be sort of like ...

id | name | sub1 |

1 | ABC | 6.27% | 0 'The first record does not need to be subtracted

2 | ABC | 7.47% | 1.2% '<= (7.47-6.27)

3 | PQR | 3.39% | 0 'The first record does not need to be subtracted

4 | PQR | 2.21% | -1.18% '<= (2.21-3.39)

Thank you so much

-1


source






More articles:


All Articles