Professional Web Applications Themes

Help with GROUP BY - MySQL

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 ...

  1. #1

    Default Help with GROUP BY

    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.

    Marston Guest

  2. #2

    Default Re: Help with GROUP BY

    > What I would like, is to get the same result, but in case it DOESN'T 

    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

    nino9stars@yahoo.com Guest

  3. #3

    Default Re: Help with GROUP BY

    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, "com" <com>
    wrote: 
    >
    > 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[/ref]


    Marston Guest

  4. #4

    Default Re: Help with GROUP BY

    > Right now I have an array of all the dates, loop over the array and 

    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.pInt(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

     [/ref]





    > [/ref]


    nino9stars@yahoo.com Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. New to this group
    By Kroyrotica in forum Macromedia Flash
    Replies: 2
    Last Post: December 30th, 07:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139