0

In mysql can we query by using the previous output data . ie.,

 select IPAddress,Sum(L2FramesSent),convert((min(datetime) div 500)*500, datetime) + INTERVAL 5 minute as endOfInterval from databasename.tablename  where datetime BETWEEN '2012-09-20 10:00:00' AND '2012-09-20 16:45:00' group by datetime div 500;

By the above query I got

+-----------+-------------------+---------------------+
| IPAddress | Sum(L2FramesSent) | endOfInterval       |
+-----------+-------------------+---------------------+
| moteid6   |            523791 | 2012-09-20 10:05:00 |
| moteid4   |            349555 | 2012-09-20 11:00:00 |
| moteid6   |            276358 | 2012-09-20 11:05:00 |
| moteid4   |            525457 | 2012-09-20 12:00:00 |
| moteid4   |            960010 | 2012-09-20 12:05:00 |
| moteid13  |           1263313 | 2012-09-20 12:10:00 |
| moteid12  |           1001343 | 2012-09-20 12:15:00 |
| moteid4   |            325904 | 2012-09-20 13:00:00 |
| moteid14  |            691390 | 2012-09-20 13:05:00 |
| moteid4   |           1028479 | 2012-09-20 15:40:00 |
| moteid12  |           1394286 | 2012-09-20 15:45:00 |
| moteid12  |           1336231 | 2012-09-20 15:50:00 |
| moteid6   |            263674 | 2012-09-20 15:55:00 |
+-----------+-------------------+---------------------+

Now I want the difference between two rows Is it possible to get it If I give this:

select (b.Sum(L2FramesSent) - a.Sum(L2FramesSent)),convert((min(a.datetime) div 500)*500, datetime) + INTERVAL 5 minute as endOfInterval from tablename.databasename a,tablename.databasename b where a.datetime BETWEEN '2012-09-12 11:00:00' AND '2012-09-12 17:05:00' group by a.datetime div 500;

I am getting error ERROR 1630 (42000): FUNCTION b.Sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

Both the tablename and databasename are the same.

Jocelyn
  • 11,209
  • 10
  • 43
  • 60
user1659213
  • 101
  • 1
  • 1
  • 5

1 Answers1

0

The mistake is here:

select (b.Sum(L2FramesSent) - a.Sum(L2FramesSent))

try

select (Sum(b.L2FramesSent) - Sum(b.L2FramesSent))

instead

a and b are aliases for your table, and sum is an (generally available) function.

RomanKonz
  • 1,027
  • 1
  • 8
  • 15
  • Thankyou,But i am getting ERROR 1305 (42000): FUNCTION Sum.b does not exist,error – user1659213 Sep 25 '12 at 09:36
  • can you paste your whole statement? – RomanKonz Sep 25 '12 at 09:37
  • select (Sum.b(L2FramesSent) - Sum.a(L2FramesSent)),convert((min(a.datetime) div 500)*500, datetime) + INTERVAL 5 minute as endOfInterval from 6panview.Statistics3 a,6panview.Statistics3 b where a.datetime BETWEEN '2012-09-12 11:00:00' AND '2012-09-12 17:05:00' group by a.datetime div 500; ERROR 1305 (42000): FUNCTION Sum.b does not exist – user1659213 Sep 25 '12 at 09:40
  • look at my answer - not "sum.b(" -> "sum(b." – RomanKonz Sep 25 '12 at 09:45
  • Thankyou,I have got something very huge values.What does the expression actually mean. – user1659213 Sep 25 '12 at 09:50
  • What i need is,I have got one table know,which contains Sum(L2FramesSent) of every 5minutess.I want the difference between each of two rows – user1659213 Sep 25 '12 at 09:53
  • sorry, i don't have time to solve this for your specific problem, but there is a solution for a similar problem: http://stackoverflow.com/questions/5078987/calculate-deltadifference-of-current-and-previous-row-in-sql – RomanKonz Sep 25 '12 at 10:04