# Getting difference of a column between first and last row with count number

I have table `mytable` as shown below

``````CREATE TABLE mytable( id, created_at, total_number )
AS VALUES
( 17330  , '2018-05-24 19:25:29'::timestamp, 26909 ),
( 46331  , '2018-05-25 00:57:34',            26914 ),
( 72131  , '2018-05-26 00:48:12',            26944 ),
( 102583 , '2018-05-27 00:53:50',            26972 );
``````

I would like to get total_number difference between first and the last

I got my first and last row with this query

``````(SELECT * FROM mytable
ORDER BY created_at ASC LIMIT 1)
UNION
(SELECT * FROM mytable
ORDER BY created_at DESC LIMIT 1)
``````

My query should return this results. How can I achieve it?

``````difference        number_of_rows   avg
(26972-26909=63)  4                63/4
``````
• You should divide by 3, not 4 because the column is titled "difference". With 4 values, you get 3 differences. If we have a value every hour (at xx:00), would you divide the diff from midnight to midnight with 25 or 24, to get the hourly average difference? May 27, 2018 at 18:03

Another variation. Here the function `first_value` is used to get both the first and last total_number (different directions):

``````select abs(lst - fst) as diff, number_of_rows
, abs(lst - fst) / number_of_rows as average
from (
select count(1) over () as number_of_rows
, first_value(total_number) over (order by created_at) fst
, first_value(total_number) over (order by created_at desc) lst
from tbl
fetch first 1 rows only
) as t;
``````

An alternative is to specify the window frame with `unbounded following` and use the function `last_value`:

``````select abs(lst - fst) as diff, number_of_rows
, abs(lst - fst) / number_of_rows as average
from (
select count(1) over () as number_of_rows
, first_value(total_number) over (order by created_at) fst
, last_value(total_number) over (order by created_at
rows between current row
and unbounded following
) lst
from tbl
fetch first 1 rows only
) as t;
``````

Perhaps it is worth noting that the default window frame is `range between unbounded preceding and current row`, so in the last example, we need to override that to truly see the last row. All rows in the sub-select are identical, so we can pick one randomly (used in both examples).

I believe limit 1 now is part of the standard, but I'm used to `fetch first ...` so I'll stick to that

In this situation, distinct could have been used instead of limit or fetch ...

``````from (
select distinct count(1) over () as number_of_rows
, ...
from tbl
)
``````

I would like to get total_number difference between first and the last

My assumption is that there is no faster way to do this if you have a sufficiently large data set and an index on `created_at`

``````SELECT tmax.max - tmin.min AS diff,
tcount.cnt AS number_of_rows,
(tmax.max - tmin.min)::double precision / tcount.cnt AS avg
FROM ( SELECT total_number FROM mytable ORDER BY created_at ASC FETCH FIRST ROW ONLY )
AS tmin(min)
CROSS JOIN ( SELECT total_number FROM mytable ORDER BY created_at DESC FETCH FIRST ROW ONLY )
AS tmax(max)
CROSS JOIN ( SELECT count(*) FROM mytable ) AS tcount(cnt);
``````

I don't know of a more terse way to tell the planner find the highest and lowest by X, retrieving value Y -- which seems to be what you want.

AFAIK, only this way can the database plan this as one index scan.

Select the `total_number` where `created_at` is the maximum or the minimum, respectively and subtract them to get the `difference`. For `number_of_rows` use `count()`. And `avg` just uses the previously built expressions in the arithmetic expression. It should look something like:

``````SELECT (SELECT mi.total_number
FROM mytable mi
WHERE mi.created_at = (SELECT max(mii.created_at)
FROM mytable mii))
-
(SELECT mi.total_number
FROM mytable mi
WHERE mi.created_at = (SELECT min(mii.created_at)
FROM mytable mii)) difference,
(SELECT count(*)
FROM mytable mi) number_of_rows,
((SELECT mi.total_number
FROM mytable mi
WHERE mi.created_at = (SELECT max(mii.created_at)
FROM mytable mii))
-
(SELECT mi.total_number
FROM mytable mi
WHERE mi.created_at = (SELECT min(mii.created_at)
FROM mytable mii)))
/
(SELECT count(*)
FROM mytable mi) avg;
``````

But you will get a problem if there is more than one row in `mytable` having the minimum (or maximum) value for `created_at`. To handle that case you need to define which one of the possible `total_number` values should be chosen then. The IDs don't look serial to me, so they cannot be used to solve such a conflict I guess.

I assume "first" and "last" value are defined by the `created_at` column. Your approach can be extended to use both sub-query and combine them into a single one:

``````with fvalue as (
-- get the first value and count the total number of rows
select id,
created_at,
total_number,
count(*) over () as number_of_rows
from mytable
order by created_at
limit 1
), lvalue as (
select id,
created_at,
total_number
from mytable
order by created_at desc
limit 1
)
select lv.total_number - fv.total_number as diff,
fv.number_of_rows,
(lv.total_number - fv.total_number) / fv.number_of_rows::decimal as avg
from fvalue fv
cross join lvalue lv
``````

The window function `count(*) over ()` counts all rows in the table and is evaluated before the `limit` clause.