Interrows average value?

[blackflicker]

Hello, I have a table which is:

DROP TABLE IF EXISTS dummy;
CREATE TABLE dummy (
  id int(11) not null auto_increment,
  entered int(11) not null default 0,
primary key(id)
);

And dummy records for you to easily test:

INSERT INTO dummy (entered) VALUES
(100), (150), (200), (250), (350);

OK !

I need to find out the average between row.entereds. In this example,

150-100 = 50
200-150 = 50
250-200 = 50
350-250 = 100

(50+50+50+100)/4 = 62.5

What a simple query do I need to find that 62.5 value?


Thanks in advance.

[chandu031]

Hello, I have a table which is:

DROP TABLE IF EXISTS dummy;
CREATE TABLE dummy (
  id int(11) not null auto_increment,
  entered int(11) not null default 0,
primary key(id)
);

And dummy records for you to easily test:

INSERT INTO dummy (entered) VALUES
(100), (150), (200), (250), (350);

OK !

I need to find out the average between row.entereds. In this example,

150-100 = 50
200-150 = 50
250-200 = 50
350-250 = 100

(50+50+50+100)/4 = 62.5

What a simple query do I need to find that 62.5 value?


Thanks in advance.

Hi blackflicker,

The following code will give you the result:

[code=sql]

select avg(b.entered - a.entered) from (select id r1,entered from dummy) a ,
(select id r2 , entered from dummy )b where r2 = r1 + 1

[/code]

I think you will have to use a different logic than just r2 = r1 + 1 as there might be no continuity in the Identity values.
I am sure you will be able to figure that logic out.

Cheers!!

[pradeep kaltari]

Hello, I have a table which is:

[CODE=]DROP TABLE IF EXISTS dummy;
CREATE TABLE dummy (
id int(11) not null auto_increment,
entered int(11) not null default 0,
primary key(id)
);[/CODE]

And dummy records for you to easily test:

INSERT INTO dummy (entered) VALUES
(100), (150), (200), (250), (350);

OK !

I need to find out the average between row.entereds. In this example,

150-100 = 50
200-150 = 50
250-200 = 50
350-250 = 100

(50+50+50+100)/4 = 62.5

What a simple query do I need to find that 62.5 value?


Thanks in advance.

Hi Blackflicker,
The following query gives the solution you require:
[code=mysql]
SELECT AVG(a.entered-b.entered) FROM (SELECT * FROM dummy) a, (SELECT * FROM dummy ) b
WHERE a.id= ( SELECT MIN(id) FROM dummy WHERE id>b.id)
[/code]

If you delete some rows then there will not be continuity in the id's. Hence, You need the SELECT statement in the where clause.
But I am not sure how efficient this query is. I hope this helps.

Regards,
Pradeep

[Motoma]

How about:
[code=sql]
SELECT (max(entered) + min(entered)) / count(*) FROM dummy GROUP BY entered
[/code]

You see, all of the interior values (everything that isn't the largest value or the smallest value) get canceled out, because they are both added and subtracted. In the end you just have the difference of the max and min averaged by the total number of entries.

[pradeep kaltari]

How about:
[code=sql]
SELECT (max(entered) + min(entered)) / count(*) FROM dummy GROUP BY entered
[/code]

You see, all of the interior values (everything that isn't the largest value or the smallest value) get canceled out, because they are both added and subtracted. In the end you just have the difference of the max and min averaged by the total number of entries.

Hi Motoma,
I guess the expected answer is 62.5 as posted by the the query postee.
I dont think your query will lead to expected results. Could you please explain your query and what were you trying to say?

Regards,
Pradeep.

[Motoma]

Hi Motoma,
I guess the expected answer is 62.5 as posted by the the query postee.
I dont think your query will lead to expected results. Could you please explain your query and what were you trying to say?

Regards,
Pradeep.

MY APOLOGIES!! It is still early here, I fired that off without testing it :P

The correct code would be:
[code=mysql]
SELECT (max(entered)-min(entered))/(count(*)-1) FROM dummy
[/code]

I forgot that there were five rows.
The logic is still the same.

[Motoma]

MY APOLOGIES!! It is still early here, I fired that off without testing it :P

The correct code would be:
[code=mysql]
SELECT (max(entered)-min(entered))/(count(*)-1) FROM dummy
[/code]

I forgot that there were five rows.
The logic is still the same.

Reexamining the problem, this solution will only work in cases where the rows are increasing in value.

[pradeep kaltari]

MY APOLOGIES!! It is still early here, I fired that off without testing it :P

The correct code would be:
[code=mysql]
SELECT (max(entered)-min(entered))/(count(*)-1) FROM dummy
[/code]

I forgot that there were five rows.
The logic is still the same.

Hi Motoma,
That was an eye opener. Good one.
(Why I didn't think in that way, initially? Anyways, I am getting lots to learn here. Thanks)

Regards,
Pradeep.

[blackflicker]

If you delete some rows then there will not be continuity in the id's. Hence, You need the SELECT statement in the where clause.
But I am not sure how efficient this query is. I hope this helps.

Regards,
Pradeep

Hello Pradeep, thanks for your time and the answer. Although not an efficiently query as you have explained, at least I have got some clues how to find it.

Actually, this is a dummy table but the real table has a lot of columns that should be displayed in the same result set that I would use from PHP, but again I have got the clues because of you and chandu031. Thanks friends.

[blackflicker]

Reexamining the problem, this solution will only work in cases where the rows are increasing in value.

Hi Motoma,

Thanks for your time. Your query is really really simple given respectful clues to me how to do the work. Besides, my real table includes a time column which would be sorted before getting an average for row continuity. Actually, the row pattern going to stay as SUM(r2-r1)/COUNT(*)-1, but the rows going to be sorted for the unix epoch time column.

[Motoma]

Hi Motoma,

Thanks for your time. Your query is really really simple given respectful clues to me how to do the work. Besides, my real table includes a time column which would be sorted before getting an average for row continuity. Actually, the row pattern going to stay as SUM(r2-r1)/COUNT(*)-1, but the rows going to be sorted for the unix epoch time column.

Okay, just remember the formula. After sorting (regardless of the values used) the average difference will be always be:

((Value of the last row) - (Value of the first row)) / ((Number of rows) - 1)