How to Subtract Two Row's Values Within Same Column Using SQL Query

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 sub2
FROM table t1 CROSS JOIN
     table t2
WHERE t1.date = '2014-11-08' AND t2.id = '2014-11-07';

SQL Subtract two rows from each other in same column to get a result

You would typically use window function lead() or lag() - if your database supports that.

The following query puts on each row the difference with the previous value of the same equipment:

select
    t.*,
    t_energy_a - lag(t_energy_a) over(partition by meter_id order by dod) diff
from mytable t

Subtract row values of 2 columns within same table in SQL query

Try this:

SELECT ID, (COM - REFUND) AS OUTPUT
FROM TABLE_NAME
WHERE SUPPORT IS NOT NULL AND STATUS = 1; 

The 'as output' names the column in your result set. The where statement filters out your rows by the conditions you have given. The FROM clause tells on which table to execute the query. You have to put your own table name.

SQL subtract same column values by conditional rows and insert new results on a table

Better option:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4273a6e920b54e118868ca44a5927cce

create table data ( amount int, month  int, year int,  id int, metric 
varchar(255) );
insert into data values (10, 8, 2020, 1, 'last');
insert into data values (20, 8, 2021, 1, 'current');
insert into data values (30, 8, 2020, 2, 'last');
insert into data values (60, 8, 2021, 3, 'current');
insert into data values (70, 9, 2020, 1, 'last');
insert into data values (0,  9, 2021, 1, 'current');
insert into data values (90, 9, 2020, 2, 'last');
insert into data values (40, 8, 2021, 2, 'current');
insert into data values (50, 8, 2020, 3, 'last');
insert into data values (0,  9, 2021, 2, 'current');
insert into data values (110, 9, 2020, 3, 'last');
insert into data values (0,  9, 2021, 3, 'current');
select * from data order by id, month, year

select * from data
union all
select
c.amount - l.amount as amount, c.month, c.year, c.id, 'subtract' as metric
from
(select * from data where year = 2021) c
left join
(select * from data where year = 2020) l
on
l.id = c.id
and
l.month = c.month
order by id, month, year, metric;

Subtract two values of same column based on another column and save the result in a new row

INSERT INTO table (yyyymm, status, bookname, quantity)
SELECT t1.yyyymm, 'AVAILABLE', t1.bookname, t1.quantity - t2.quantity
FROM table t1
JOIN table t2 USING (yyyymm, bookname)
WHERE t1.status = 'TOTAL'
  AND t2.status = 'RENTED OUT'

table (yyyymm, status, bookname) must be unique by proper constraint/index.

The record will be inserted only when both source records are present. If it is possible that 'RENTED OUT' is absent and its value must be assumed as zero, use LEFT JOIN ans COALESCE additionally (and move the condition by t2.status to ON clause).

---

Can you please be kind enough to edit your answer to include the assumption when 'RENTED OUT' is absent?

INSERT INTO table (yyyymm, status, bookname, quantity)
SELECT t1.yyyymm, 'AVAILABLE', t1.bookname, t1.quantity - COALESCE(t2.quantity, 0)
FROM table t1
LEFT JOIN table t2 ON t1.yyyymm = t2.yyyymm
                  AND t1.bookname = t2.bookname
                  AND t2.status = 'RENTED OUT'
WHERE t1.status = 'TOTAL'

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

If you are using 2012 version of SQL Server or newer you could use:

select id, name, value,
       coalesce(value - lag(value) over (partition by (select null) order by id), 0) diff
from my_table

If LAG is not available, then use:

select t1.*, coalesce(t1.value - t2.value, 0) diff
from my_table t1
left join my_table t2 on t1.id = t2.id + 1

COALESCE justmakes sure, that you get 0 when there's null (which means, there isn't previous row).

How can I subtract two rows in SQL based on a shared column value?

You could use CTEs to store the child and parent values and then query such as:

with child as (
  select batch, result
  from my_table
  where run = 2
  ), 
parent as (
  select batch, result
  from my_table
  where run = 1
  )
select t.sample, t.result, t.batch, t.run, c.result - p.result as diff
from my_table t
left join child c
  on t.batch = c.batch
left join parent p
  on t.batch = p.batch

Output:

---