How to Calculate Between Different Group of Rows of the Same Table

How to calculate between different group of rows of the same table

Your query doesn't work because you're doing a CROSS JOIN (because of ,) on the tables, so each row is matched with every row in the other table, rather than an INNER JOIN on the month.

Modifying your query:

select a.month, a.value-b.value
from
  (select month, value from table where year = 2012) a
  JOIN
  (select month, value from table where year = 2011) b
  ON a.month = b.month

A faster query:

select a.month, a.value-b.value
from
  yourTable a
  join yourTable b
    on a.month = b.month
  where a.year = 2012 and b.year = 2011

For multiple rows per month for each year:

select a.month, a.value-b.value
from
  (select month, sum(value) as value
   from yourTable where year = 2012
   group by month) a
  join
  (select month, sum(value) as value
   from yourTable where year = 2011
   group by month) b
    on a.month = b.month

SQLFiddle.

How to calculate between different group of rows of the same table group by 1 value

Just join with where:

select a.date, b.date, a.numberfield, b.numberfield,
       (b.days - a.days) as difference
from #tmp a join
     #tmp b
     on a.numberfield = b.numberfield
where a.date = '2020-04-11' and b.date = '2020-04-12' and
      abs(b.days - a.days) > 1
order by 3;

The subqueries are not needed . . . nor are they particularly helpful.

Displaying the difference between rows in the same table

If you give each row a row number in a CTE then join on yourself to the next row you can compare the old and the new values. Unioning the 2 different column names is a bit clunky however, if you needed a more robust solution you might look at pivoting the data.

You also obviously have to convert all values to a common datatype e.g. a string.

declare @Test table (emp_audit_id int, eid int, [name] varchar(32), salary money);

insert into @Test (emp_audit_id, eid, [name], salary)
values
(1, 1, 'Daniel', 1000),
(2, 1, 'Dani', 1000),
(3, 1, 'Danny', 3000);

with cte as (
    select emp_audit_id, eid, [name], salary
      , row_number() over (partition by eid order by emp_audit_id) rn
    from @Test
)
select C.emp_audit_id, 'name' columnName, P.[Name] oldValue, C.[name] newValue
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.[name],'') != coalesce(P.[Name],'')
union all
select C.emp_audit_id, 'salary' columnName, convert(varchar(21),P.salary), convert(varchar(21),C.salary)
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.salary,0) != coalesce(P.salary,0)
order by C.emp_audit_id, columnName;

Returns:

emp_audit_id	columnName	oldValue	newValue
1	name	NULL	Daniel
1	salary	NULL	1000.00
2	name	Daniel	Dani
3	name	Dani	Danny
3	salary	1000.00	3000.00

Calculating the Difference Between Two Values in the Same Column using group by in mysql

Your sample data is a little suspect but I think you basically need to apply a case expression to your sale type:

select name,
    Sum(amount * case when type='return' then -1 else 1 end) Amount
from t
group by name;

Calculate difference between values in consecutive rows by group

The package data.table can do this fairly quickly, using the shift function.

require(data.table)
df <- data.table(group = rep(c(1, 2), each = 3), value = c(10,20,25,5,10,15))
#setDT(df) #if df is already a data frame

df[ , diff := value - shift(value), by = group]    
#   group value diff
#1:     1    10   NA
#2:     1    20   10
#3:     1    25    5
#4:     2     5   NA
#5:     2    10    5
#6:     2    15    5
setDF(df) #if you want to convert back to old data.frame syntax

Or using the lag function in dplyr

df %>%
    group_by(group) %>%
    mutate(Diff = value - lag(value))
#   group value  Diff
#   <int> <int> <int>
# 1     1    10    NA
# 2     1    20    10
# 3     1    25     5
# 4     2     5    NA
# 5     2    10     5
# 6     2    15     5

For alternatives pre-data.table::shift and pre-dplyr::lag, see edits.

Select 2 counts from the same table with a foreign key involved

Use a subquery to calculate the number of students in each classroom -- including no students at all. Then aggregate again:

select count(*) as num_classrooms,
       sum(case when num_students >= 2 then 1 else 0 end)
from (select cr.id, count(ch.classroom_id) as num_students
      from classroom cr left join
           child ch
           on ch.classroom_id = cr.id and
              ch.is_active = true
      group by cr.id
     ) x;

Note: I'm not sure if is_active is for the child or the classroom. This assumes it is for the child.

Find the time difference between two consecutive rows in the same table in sql

I have solved this for similar problems and it need not be that the rows even be sorted:

select t1.EmpID, t1.TimeIn, t1.TimeOut, 
       datediff(minute, max(t2.TimeOut), t1.TimeIn) as minutes
from timesheet t1 left join timesheet t2 on t1.EmpID = t2.EmpID 
       and t2.TimeOut < t1.TimeIn
group by t1.EmpID, t1.TimeIn, t1.TimeOut

Let me know if this works.

Here is a sql fiddle: http://sqlfiddle.com/#!3/89a43/1

Calculate delta's between different rows in same table

This is a breeze with dplyr using the lag function. Assuming the columns in your dataframe are named UCPTlogTime, Address, Meter, and UCPTvalue:

library(dplyr)

data <- data %>% group_by(Address, Meter) %>% 
  mutate(delta = order_by(UCPTlogTime, UCPTvalue - lag(UCPTvalue))) %>%
  mutate(delta = ifelse(is.na(delta), 0, delta))

Calculate Time Difference Between Two Rows

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
        FROM    mytable
        )
SELECT  DATEDIFF(second, mc.DataDate, mp.DataDate)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

In SQL Server 2012+:

SELECT  DATEDIFF(second, pDataDate, dataDate)
FROM    (
        SELECT  *,
                LAG(dataDate) OVER (ORDER BY dataDate) pDataDate
        FROM    rows
        ) q
WHERE   pDataDate IS NOT NULL