Database
Visitor

## Query help to calculate sum of delta over groups ordered by time

Hello Experts,

I am new to teradata and trying to figure out how to calcualte sum of delta over groups when data needed to ordered by time.

I am attaching two pictures one with source data and in it I new to calculate a column as shown in green for the delta's and retrieve data as shown in expected result by doing the sum of deltas.

Thanks,

Naveen

Source Data:

Expected Result:

2 REPLIES 2
Highlighted
Enthusiast

## Re: Query help to calculate sum of delta over groups ordered by time

select equipment,group,sum(measurement) over (partition by group) as  sumof change from tablename;

Note:

This query written in sql server. Change it according to Teradata

## Re: Query help to calculate sum of delta over groups ordered by time

Based on your example data it looks like the measurement is always increasing, then it's a simple MAX-MIN to get the difference:

```SELECT  equipment, subgroup, group,
Max(measurement)-Min(measurement) AS diff
FROM tab
group BY 1,2,3
HAVING Count(*) > 1```

Otherwise you need to find the first and last row within a group and subtract them:

```SELECT equipment, subgroup, group,
Last_Value(measurement) -- value in last row
Over (PARTITION BY equipment, subgroup, group
ORDER BY time
ROWS BETWEEN Unbounded Preceding AND Unbounded Following)
- measurement AS diff
FROM tab
QUALIFY
Row_Number() -- return 1st row only
Over (PARTITION PARTITION BY equipment, subgroup, group
ORDER BY time) = 1
AND -- if there's more than one row
Count(*) Over (PARTITION PARTITION BY equipment, subgroup, group) > 1```

Now you got the difference and then it's a simple:

```SELECT equipment, group, SUM(diff)
FROM (previous select) as dt
GROUP BY 1,2```