Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Question Unanswered: Query Months help

    Hey all i am in need of some helpful tips/advice on how to go about my problem. I have a database that houses a "signup" table. The date for this table is formated as such:
    2010-04-03 00:00:00
    Now suppose i have 10 records in this database:
    2010-04-03 00:00:00
    2010-01-01 00:00:00
    2010-06-22 00:00:00
    2010-02-08 00:00:00
    2010-02-05 00:00:00
    2010-03-08 00:00:00
    2010-09-29 00:00:00
    2010-11-16 00:00:00
    2010-04-09 00:00:00
    2010-05-21 00:00:00
    And i wanted to get each months total registers... so following the example above:
    Jan = 1
    Feb = 2
    Mar = 1
    Apr = 2
    May = 1
    Jun = 1
    Jul = 0
    Aug = 0
    Sep = 1
    Oct = 0
    Nov = 1
    Dec = 0
    Now how can i use a query to do that but not have to use a query like:
    Code:
    WHERE left(date, 7) = '2010-01'
    and keep doing that 12 times? I would like it to be a single query call and just have it place the months visits into a array like so:
    Code:
    do until EOF
        theMonthArray[0] = "total for jan"
        theMonthArray[1] = "total for feb"
        theMonthArray[2] = "total for mar"
        theMonthArray[3] = "total for apr"
        ...etc
    loop
    I just can not think of a way to do that other than the example i posted with the 12 query called-one for each month.

    Any help would be great!

    David

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi David,

    try the following DATE_FORMAT function in MySQL to convert the date to a format that is good for you. In the example above:

    SELECT DATE_FORMAT(fieldname, '%b') AS MONTH, COUNT(1) AS RESULT
    FROM tablename
    GROUP BY DATE_FORMAT(fieldname, '%b');

    Hope this helps?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Quote Originally Posted by it-iss.com View Post
    Hi David,

    try the following DATE_FORMAT function in MySQL to convert the date to a format that is good for you. In the example above:

    SELECT DATE_FORMAT(fieldname, '%b') AS MONTH, COUNT(1) AS RESULT
    FROM tablename
    GROUP BY DATE_FORMAT(fieldname, '%b');

    Hope this helps?
    Thanks for the reply, it-iss.com. However, i do not think that will output each month separately, right?

    David

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If an entry exists for a month then it will return a row. For instance, if you have a table

    2010-04-03 00:00:00
    2010-01-01 00:00:00
    2010-06-22 00:00:00

    Then this will return
    Apr - 1
    Jan - 1
    Jun - 1

    If there are no records for a particular month then no result will be returned for that month. If you can provide a small example of what you are looking for like in your original post I may be able to help you more.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Quote Originally Posted by it-iss.com View Post
    If an entry exists for a month then it will return a row. For instance, if you have a table

    2010-04-03 00:00:00
    2010-01-01 00:00:00
    2010-06-22 00:00:00

    Then this will return
    Apr - 1
    Jan - 1
    Jun - 1

    If there are no records for a particular month then no result will be returned for that month. If you can provide a small example of what you are looking for like in your original post I may be able to help you more.
    This is my query as of right now. Again, this only populates for one month where i am trying to populate all 12 months all at once.
    Code:
    SELECT count(idNumber) as numVisits, theAccount, signUpDate, theActive
    from userinfo
    WHERE theActive = 'YES'
    AND idNumber = '0203'
    AND theAccount = 'SUB'
    AND left(signUpDate, 7) = '2010-04'
    GROUP BY idNumber
    ORDER BY numVisits;
    The example query above outputs this:
    Code:
    numVisits | theAccount | signUpDate          | theActive
    2           SUB          2010-04-16 00:00:00   YES
    Which is correct because i have 2 records within the month of April.

    But again, i am trying to do all 12 months at one time (in a single query) so i do not tax the database server as much when compared to doing 12 different query's...

    David

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi David,

    try

    SELECT DATE_FORMAT(signUpDate, '%Y-%m'), count(idNumber) as numVisits
    from userinfo
    WHERE theActive = 'YES'
    AND idNumber = '0203'
    AND theAccount = 'SUB'
    GROUP BY DATE_FORMAT(signUpDate, '%Y-%m')
    ORDER BY numVisits;

    You do not need to return idNumber, theAccount and theActive as you are putting these into the query so you know these already. This will return

    2010-01 - 1
    2010-02 - 10 ...

    If months are missing it means that there is no data for that particular month.

    If this is not what you are looking for if you can provide me with the output that you are looking for?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Quote Originally Posted by it-iss.com View Post
    Hi David,

    try

    SELECT DATE_FORMAT(signUpDate, '%Y-%m'), count(idNumber) as numVisits
    from userinfo
    WHERE theActive = 'YES'
    AND idNumber = '0203'
    AND theAccount = 'SUB'
    GROUP BY DATE_FORMAT(signUpDate, '%Y-%m')
    ORDER BY numVisits;

    You do not need to return idNumber, theAccount and theActive as you are putting these into the query so you know these already. This will return

    2010-01 - 1
    2010-02 - 10 ...

    If months are missing it means that there is no data for that particular month.

    If this is not what you are looking for if you can provide me with the output that you are looking for?
    Yes, it-iss.com, that is what i had in mind... but how do i seperate those values to place them into an array for each month? As in my example in my first post:
    Code:
    do until EOF
        theMonthArray[0] = "total for jan"
        theMonthArray[1] = "total for feb"
        theMonthArray[2] = "total for mar"
        theMonthArray[3] = "total for apr"
        ...etc
    loop
    Im sure i could do this:
    Code:
    do until EOF
         if left(rst("signUpDate"), 7) = "2010-01" then
              theMonthArray[0] = rst("numVisits")
         elseif left(rst("signUpDate"), 7) = "2010-02" then
              theMonthArray[1] = rst("numVisits")
         etc etc....
    loop
    Unless someone else knows a better way of doing that? Also, the EOF would already be after the first loop since its gathering all the data in one swoop, right?

    David

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Now I understand. If you setup the array beforehand setting all statistics to 0. Then using the query but changing the DATE_FORMAT(signUpDate, '%m') gives you the index in which you must set the values. For instance, theMonthArray[rst["month_index"]-1] = rst["numVisits"].
    This way you will have the array correctly setup.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Quote Originally Posted by it-iss.com View Post
    Now I understand. If you setup the array beforehand setting all statistics to 0. Then using the query but changing the DATE_FORMAT(signUpDate, '%m') gives you the index in which you must set the values. For instance, theMonthArray[rst["month_index"]-1] = rst["numVisits"].
    This way you will have the array correctly setup.
    I really dont understand how to go about doing what you suggested, it-iss.com...

    David

  10. #10
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi David,

    I am assuming you are using PHP here?

    // Initialize the array to 0
    for($i=0; $i<12; $i++) {
    theMonthArray[$i] = 0;
    }

    // Issue select statement
    SELECT DATE_FORMAT(fieldname, '&#37;b') AS MONTH, COUNT(1) AS RESULT
    FROM tablename
    WHERE theActive = 'YES'
    AND idNumber = '0203'
    AND theAccount = 'SUB'
    GROUP BY DATE_FORMAT(fieldname, '%b');

    // This returns two pieces of the information, the month in terms of 01 and a number, 02 and a number, ....
    // So we loop through and this will set your array correctly
    do until EOF
    theMonthArray[rst["MONTH"]-1] = rst("numVisits")
    loop

    The month returned 01 represents January but in our array this is indexes at 0 so we need to subtract 1 to get this working correctly. The month may return as a text value in which case we would need to convert that to a numeric before subtracting the 1.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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