CODEWITHSUNDEEP
mysql
nebkas
nebkas

Reputation: 23

MySQL substract first row from last row of a group

I'm working on creating Google Charts from MySQL datasource. This works fine so far. Now I want to count how many blocks of a Blockchain processed per hour.

How can I simple substract the first row from last row of a group?

SELECT
   date_format(time,'%Y-%m-%d %H-%i'),blocks,
   count(1)
FROM blockchain
GROUP BY 1  
ORDER BY `date_format(time,'%Y-%m-%d %H-%i')` ASC

And if this done, how to repeat this for the last 24 hours?

Sample data:

id          time                        blocks

3           2020-12-30 11:21:53         112149
4           2020-12-30 11:21:55         112150
5           2020-12-30 11:21:56         112150
6           2020-12-30 11:21:57         112150
7           2020-12-30 11:24:01         112169
8           2020-12-30 11:25:01         112178
9           2020-12-30 11:26:01         112188
10          2020-12-30 11:27:01         112197



-10         2020-12-30 11:27:01         112197
 3          2020-12-30 11:21:53         112149
-----------------------------------------------
                                        48

I hope its not a stupid question. I'm kinda new to this.

Upvotes: 1

Views: 55

Answers (1)

zedfoxus
zedfoxus

Reputation: 37059

You can use this query to get blocks per hour.

select hour(time) as hr, max(blocks)-min(blocks) as blocks_per_hour
from test
group by hour(time)

Result:

hr blocks_per_hour
11 48

Example: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=96ee323e8f15af0a946ff1220af01588

Upvotes: 1

Related Questions