Looking for a mysql query to count all rows previous to last month

I'll skip the "why" for brevity. But if you want to know you can ask. But basically my first quest was to find a way to count all the rows previous to the current month. I found the solution to this that works, which is:

SELECT COUNT(*) FROM myTable 
WHERE log_date < (CURDATE() -  DAYOFMONTH(CURDATE()))

I just subtract the number of days that has transpired this month and count all rows with date less than the first day of this month.

The next problem is that I need to count all rows before last month. I guess technically I could take my result above and query for results of last month and subtract the two que开发者_开发知识库ries. But I was hoping for a single query for this. I have tried variations of the two versions below:

SELECT COUNT(*) FROM myTable 
WHERE log_date < ((CURDATE()- DAYOFMONTH(CURDATE())) - INTERVAL 1 MONTH)

and

SELECT COUNT(*) FROM myTable 
WHERE log_date < DATE_SUB((CURDATE()- DAYOFMONTH(CURDATE())), INTERVAL 1 MONTH)

Both of these approaches along with some variations of this I have tried yield a result of 0 when I know that the answer should be 14.

Is there a way to get the answer I am looking for in a single query?

Thanks.

Try this:

SELECT COUNT(*) FROM `myTable` WHERE `log_date` BETWEEN "2010-11-01 00:00:00" AND "2010-11-30 23:59:59"

Ok, I think I get what you want.

You have 2 dates and want the COUNT for records before each date.

SELECT
SUM(IF(log_date<DATESUB(CURDATE()-DAYOFMONTH(CURDATE())), 1, 0)) AS LAST_MONTH,
SUM(IF(log_date<DATE_SUB((CURDATE()-DAYOFMONTH(CURDATE())),INTERVAL 1 MONTH),1,0) 
AS PREV_MONTH
FROM ....
WHERE log_date < DATESUB(CURDATE() - DAYOFMONTH(CURDATE()));

This way you have 2 emulated COUNTs by using SUM combined with IF.

I'm assuming you are using MySQL but this could work on other DBMS

Hope this helps!

I found the solution. Thank you goreSplatter and ajreal and dcestari for you input and helping me think through it. Here is the working query:

$query = mysql_query("SELECT SUM(IF(log_date<(CURDATE()-DAYOFMONTH(CURDATE())),1,0)) AS LAST_MONTH, SUM(IF(log_date<DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)), INTERVAL 1 DAY),1,0)) AS PREV_MONTH FROM myTable WHERE member_id = '$mid'")or die(mysql_error());

For PREV_MONTH calculation I went back two months, got the last day of that month and added one day to get the first day of last month. Seems like if they have a "LAST_DAY" they would also have a 'FIRST_DAY' function.

Hope this will serve to help someone down the road.