Subtracting One Row of Data from Another in SQL

Subtracting one row of data from another in SQL

This might help you (somewhat).

select a.id, a.length, 
coalesce(a.length - 
    (select b.length from foo b where b.id = a.id + 1), a.length) as diff
from foo a

Subtracting Data of current row from Next row in Sql

You need to use a PARTITION in your OVER clause to get LAG to only look at that states data:

lag(confirmed_cases) over (partition by state_name order by confirmed_cases)

Demo on dbfiddle

Note you can generally give a default value for lag to avoid the coalesce:

lag(confirmed_cases, 1, 0) over (partition by state_name order by confirmed_cases)

Demo on dbfiddle

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.

Subtracting ROW 2 FROM ROW 1

For the given data where the REPORT_DATE for 1st row is always 2019-10-08 and 2nd row is always 2019-10-06 for the rows with the same TABLE_NAME

select t1.table_name, t1.count_rows - t2.count_rows
from T t1, T t2
where t1.table_name = t2.table_name
and t1.report_date = to_date('2019-10-08', 'YYYY-MM-DD')
and t2.report_date = to_date('2019-10-06', 'YYYY-MM-DD');

If the REPORT_DATE can be any arbitrary date but the 1st row date is always greater than the 2nd row date:

select t1.table_name, t1.count_rows - t2.count_rows
from T t1, T t2
where t1.table_name = t2.table_name
and t1.report_date > t2.report_date;

Subtracting one row from another in MySQL

I am a fan of using correlated subqueries for this purpose. In your case, this would be applied as:

select t.*,
       (`EU.1` - (select `EU.1`
                  from table t2
                  where t2.date < t.date
                  order by t2.date desc
                  limit 1
                 )
       ) as diff
from table t;

The only difference is that the first value will be NULL instead of 0 -- which makes more sense to me. If you want 0, use coalesce() around the whole expression.

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;

How to subtract column value in next row SQL server

You can use ANSI standard OLAP functions as below to get your desired result.

SELECT x,
       min(x) over(
                   ORDER BY id ROWS BETWEEN 1 following AND 1 following) - x AS RESULT
FROM table1;

The above query sorts the result by id and subtract row n from row n + 1 and displays result along with row n

Sample data along with Result:

x                   RESULT
-----------------------------------
318963.0000000000   -95.9999999990
318867.0000000010   -128.0000000000
318739.0000000010   128.0000000000
318867.0000000010   NULL

DEMO

To subtract a previous row value in SQL Server 2012

Use a order by with column(s) to get consistent results.

Use lag function to get data from previous row and do the subtraction like this:

with t
as (
    select ROW_NUMBER() over (order by _date) [Sno],
        _Date,
        sum(Payment) Payment
    from DailyPaymentSummary
    group by _date
    )
select *,
    Payment - lag(Payment, 1, 0) over (order by [Sno]) diff
from t;

---

Related Topics