# Selecting number of price increases

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

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

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.

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)

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.

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 )``````

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