Query to find the difference b/w averages

[lildiapaz]

Hi,

I have wrote two queries, one to find the average for one date, and the second query to find the average for another date. And I stored the results of the query to a local string object. Is there anyway to compare the two averages by getting the difference between the two.

example query

 
String myQuery = "" +
        "select name, age, date, avg(count) from product where date=interval -7 Day + '2001-08-13'  " +
        " group by name "; 
 

Let's say that I get another query for the next 7 days, how do I compare the two averages from the two queries?

Note: Using Mysql 5.0, and JDBC app to connect to DB, table, etc (works fine)
just need help with the query in my program
Thanks,

[r035198x]

Why not just compare the values that you have stored in those local string "objects" that you saved them to?

[lildiapaz]

Thanks for the quick response, but how do I accomplish that? Can you provide a code snippet, or refer me to an article?

[r035198x]

You convert a string to a number using Integer.parseInt. You then compare the integers using the normal comparison operators.

[lildiapaz]

Thanks, I was able to make the conversion from string to int. But my next question is how do I write the sql query to query the database to return the results using the new averages in which I converted to integers?


//newdailyCount is a string like "1.2222"
//firstAverage is an int

firstAverage=Integer.parseInt(newdailyCount);

Here is the query I tried to return the results after I get the avg of the previous seven days, and the next seven days, then I want to subtract the avgs of the two. But the query doesn't work.

# String myQuery = "" +
#         "select name, age, date, avg(count) from product where newdailyCount=(firstAverage -10) " +
#         " group by name "; 

Thanks,

[lildiapaz]

Can someone provide a code snippet or suggestions for the query above?

thanks,