First let me say that for what I want to do, I"m not even sure GROUP
BY is what I need, but it seems to work in a certain case.

I have this query:

SELECT avg(reading) FROM readings WHERE date BETWEEN '2007-05-01' AND
'2007-05-31' AND user_id = 1 GROUP BY date;

Which works fine, it gives me the averages for this user on any date
between the two it finds records for, this is the certain case it
works.

What I would like, is to get the same result, but in case it DOESN'T
find records for a given date in-between the date range it simply
returns 0. So if I query a date range with 30 dates in it, I want to
get 30 avg() results back and if there were no results for a given
date in the range have it return 0. What is the best way to make this
happen in 1 query? I'm not sure of how to make this happen via pure
SQL. Thanks.

> What I would like, is to get the same result, but in case it DOESN'T
> find records for a given date in-between the date range it simply
> returns 0. So if I query a date range with 30 dates in it, I want to
> get 30 avg() results back and if there were no results for a given
> date in the range have it return 0. What is the best way to make this
> happen in 1 query? I'm not sure of how to make this happen via pure
> SQL. Thanks.

Based on my understanding, I don't think it's possible to do this as
an SQL command. However, it should be very simple in whatever code you
are writing. All you have to do is a check for an empty result and
fill in a 0 if nothing is returned. Using Java it would be like this:

String db_query = "your select statement goes here";
ResultSet db_result = stmt.executeQuery(db_query);
if (db_result.next()) {
//Here would be the function for a returned value
} else {
//Here would be the 0 return
}

Hope that helps. I've always done it this way, so I will be checking
back to see if anyone else has another solution!

Nino

Hey Nino,

Thanks for the tip. I'm coding this in Rails/Ruby.

Right now I have an array of all the dates, loop over the array and
run a separate SELECT SQL per date in the array, it is quite
inefficient and wanted to see if I could do it in one SQL query.

Bummer I can't. Is your Java example (sorry, don't know Java)
effectively the same thing I'm already doing? Thanks for the help.

On May 21, 7:24 pm, "nino9st...@yahoo.com" <nino9st...@yahoo.com>
wrote:
> > What I would like, is to get the same result, but in case it DOESN'T
> > find records for a given date in-between the date range it simply
> > returns 0. So if I query a date range with 30 dates in it, I want to
> > get 30 avg() results back and if there were no results for a given
> > date in the range have it return 0. What is the best way to make this
> > happen in 1 query? I'm not sure of how to make this happen via pure
> > SQL. Thanks.
>
> Based on my understanding, I don't think it's possible to do this as
> an SQL command. However, it should be very simple in whatever code you
> are writing. All you have to do is a check for an empty result and
> fill in a 0 if nothing is returned. Using Java it would be like this:
>
> String db_query = "your select statement goes here";
> ResultSet db_result = stmt.executeQuery(db_query);
> if (db_result.next()) {
> //Here would be the function for a returned value} else {
>
> //Here would be the 0 return
>
> }
>
> Hope that helps. I've always done it this way, so I will be checking
> back to see if anyone else has another solution!
>
> Nino

> Right now I have an array of all the dates, loop over the array and
> run a separate SELECT SQL per date in the array, it is quite
> inefficient and wanted to see if I could do it in one SQL query.
>
> Bummer I can't. Is your Java example (sorry, don't know Java)
> effectively the same thing I'm already doing? Thanks for the help.

Yeah... pretty much. You are doing a check on each date, and then you
know if you actually get a zero, right? Hmmm... If it's any
consolation (until I, or someone else, can come up with a better
solution), you could always use prepared statements to ensure better
performance. Just prepare your select statement and it should breeze
through your request. Here's an example (I use this a lot with these
kinds of queries):

PreparedStatement pstmt;
pstmt = con.prepareStatement("SELECT fname,lname FROM users WHERE
userid=?");
<your array for loop goes here>
pstmt.setInt(1, java.lang.Integer.parseInt(allscu[x]));
db_result = pstmt.executeQuery();
<end loop>

Only other thing I could think of is to create a database table with
all the dates, and then do a join on the dates table with your single
query. I believe if you join on the dates table it should return a
zero if nothing is found. Not sure if it's faster or if it would work
right, but it would get you one sql statement and do all the work on
the database side...

Nino


> On May 21, 7:24 pm, "nino9st...@yahoo.com" <nino9st...@yahoo.com>
> wrote:
>
> > > What I would like, is to get the same result, but in case it DOESN'T
> > > find records for a given date in-between the date range it simply
> > > returns 0. So if I query a date range with 30 dates in it, I want to
> > > get 30 avg() results back and if there were no results for a given
> > > date in the range have it return 0. What is the best way to make this
> > > happen in 1 query? I'm not sure of how to make this happen via pure
> > > SQL. Thanks.
>
> > Based on my understanding, I don't think it's possible to do this as
> > an SQL command. However, it should be very simple in whatever code you
> > are writing. All you have to do is a check for an empty result and
> > fill in a 0 if nothing is returned. Using Java it would be like this:
>
> > String db_query = "your select statement goes here";
> > ResultSet db_result = stmt.executeQuery(db_query);
> > if (db_result.next()) {
> > //Here would be the function for a returned value} else {
>
> > //Here would be the 0 return
>
> > }
>
> > Hope that helps. I've always done it this way, so I will be checking
> > back to see if anyone else has another solution!
>
> > Nino