get the different value from sql

Question

i have the following table

id   rack        usage    day
1    rack1       100      20141210
2    rack1       120      20141211
3    rack1       135      20141212  
4    rack1       144      20141213
5    rack1       160      20141214
6    rack1       165      20141215
7    rack1       167      20141216

i would like to get the diffrent of the usage per day. how to acomplish with select query? so it will result

day       usage
20141210  0
20141211  20
20141212  15
20141213  9
20141214  16
20141215  5
20141216  3

possible duplicate of [How do I lag columns in MySQL?](http://stackoverflow.com/questions/5483319/how-do-i-lag-columns-in-mysql) — RandomSeed, Dec 26 '14 at 13:12

JC Sama · Accepted Answer · 2014-12-26T22:57:43.780

0

Try this out, it should work :

select day, usage, id as id_rack,
usage - (select usage from my_table t where t.id < id_rack order by t.id desc limit 1) as usage
from my_table

edited Dec 26 '14 at 22:57

answered Dec 26 '14 at 12:32

JC Sama

2,214
1
13
13

What do you mean by "diffrent" !! – JC Sama Dec 26 '14 at 14:11
@lainard I think you mean *difference* of usage between each day? – hyde Dec 26 '14 at 14:32
Yes the date is on the row day not date – lainard Dec 26 '14 at 16:04
You mean average of use per day? if so then try : SELECT day, avg(usage) as usage FROM my_table GROUP BY day – JC Sama Dec 26 '14 at 16:11
No but the diffrential each day, i have change the table above to make it easier to understand – lainard Dec 26 '14 at 18:02
select day, usage, id as id_rack, usage - (select usage from my_table t where t.id < id_rack order by t.id desc limit 1) as usage from my_table – JC Sama Dec 26 '14 at 22:57
@Simo Thank you but unfortunetly it gives me error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'usage, id as id_rack, usage - (select usage from my_table t where t.id < id_rack' at line 1 – lainard Dec 27 '14 at 08:32
@Simo Thank you this is actually what i need. awsome sorry above comment cant be edited – lainard Dec 27 '14 at 08:44
Glad that I could help ^^ – JC Sama Dec 27 '14 at 13:05

score 0 · Answer 2 · answered Dec 26 '14 at 12:46

0

try this:

SELECT day, sum(usage) as usage FROM tbl_name GROUP BY day;

answered Dec 26 '14 at 12:46

Pathik Vejani

4,263
8
57
98

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – RandomSeed Dec 26 '14 at 13:07
@RandomSeed even if this were incorrect, they've still attempted to solve the question. I would consider this an answer. – AdamMc331 Dec 26 '14 at 16:34
I have simplied the table to make it undestandable i would like to get the difference of the usage each day – lainard Dec 26 '14 at 18:02

score 0 · Answer 3 · answered Dec 26 '14 at 14:37

0

select t1.day as day, (t2.usage-t1.usage) as usage from table t1, table t2 where date_format(date_add(t1.date, interval 1 day),'%Y%m%d')=t2.date;

Check this query.

answered Dec 26 '14 at 14:37

Veerasamy Kaunder

11
1

score 0 · Answer 4 · answered Dec 26 '14 at 17:27

0

Try this

SELECT l.day,l.usage -
                    (SELECT x.usage 
                    FROM testing x
                    WHERE x.rackid < l.rackid
                    ORDER BY rackid DESC
                    LIMIT 1)
FROM testing l

answered Dec 26 '14 at 17:27

jay.jivani

1,560
1
16
33

thanks Jivani closer to the what i need but not the correct one – lainard Dec 26 '14 at 18:17

get the different value from sql

4 Answers4