Selecting number of price increases

Databases
1

I have a table of items having same items with multiple prices by date and time. How can i calculate number of price increases of all the items. Below is a sample table

id, name , price, date_time
1, E1, 0.03193, 2021-02-23 10:58:47
2, E2, 0.03194, 2021-02-23 10:58:47
3, E3, 0.03195, 2021-02-23 10:58:47

4, E1, 0.03196, 2021-02-23 11:50:47
5, E2, 0.03198, 2021-02-23 11:50:47
6, E3, 0.03199, 2021-02-23 11:50:47

7, E1, 0.03191, 2021-02-23 08:48:47
8, E2, 0.03190, 2021-02-23 08:48:47
9, E3, 0.03192, 2021-02-23 08:48:47

10, E1, 0.03183, 2021-02-22 06:48:47
11, E2, 0.03190, 2021-02-22 06:48:47
12, E3, 0.03192, 2021-02-22 06:48:47

E1 will have 2
E2 will have 1
E3 will have 1

Each item will compare price with the price of its previous date/time entry

2

do prices ever decrease?

if a price decreases and then increases back to where it was, does that count as an increase?

oh, and your sample data is misleading

here are the changes for E1 –

E1, 0.03183, 2021-02-22 06:48:47
E1, 0.03191, 2021-02-23 08:48:47
E1, 0.03193, 2021-02-23 10:58:47
E1, 0.03196, 2021-02-23 11:50:47

looking at the datetimes and corresponding prices, i would say there are 3 increases, not two

3

That’s correct there are 3 increases and not 2, sorry for misleading.

And if a price decreases and then increases back to where it was, it will not consider as increase.

5

What if that return to a previous value is days later? weeks? years?

Are you trying to count the number of times a new record is set? (This seems to be roughly what you are describing)

6

This keeps the data for only 24 hours so it won’t cause me issue. Yes i only need to count the number of times there is a price raise of an item by comparing the price of new with its old prices in the database.

7

try this, see if it works for you –

SELECT COUNT(*) AS number_of_times
  FROM yourtable AS that
 WHERE EXISTS 
       ( SELECT 'vwalah'
           FROM yourtable AS this
          WHERE this.name      = that.name
            AND this.date_time < that.date_time 
            AND this.price     < that.price )
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.