Query Months help

StealthRT · #1 (**permalink**) 04-14-10, 04:00

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:

Quote:

2010-04-03 00:00:00

Now suppose i have 10 records in this database:

Quote:

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:

Quote:

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

it-iss.com · #2 (**permalink**) 04-14-10, 04:15

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?

StealthRT · #3 (**permalink**) 04-14-10, 04:18

Quote:

Originally Posted by it-iss.com

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

it-iss.com · #4 (**permalink**) 04-14-10, 04:24

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.

StealthRT · #5 (**permalink**) 04-14-10, 04:34

Quote:

Originally Posted by it-iss.com

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

it-iss.com · #6 (**permalink**) 04-14-10, 04:52

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?

StealthRT · #7 (**permalink**) 04-14-10, 05:08

Quote:

Originally Posted by it-iss.com

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

it-iss.com · #8 (**permalink**) 04-14-10, 05:18

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.

StealthRT · #9 (**permalink**) 04-14-10, 16:07

Quote:

Originally Posted by it-iss.com

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

it-iss.com · #10 (**permalink**) 04-15-10, 02:41

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, '%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.

Internet Services

Web Development

Digital Marketing

Consumer Tech