Need some help calculating date values

[duckduckgoose]

I hear that it's better to put a date calculation directly into the query statement, which is what I'm trying to do. Basically, I want to supply a given date and then find year-to-date, month-to-date, and week-to-date values based on that date.

So, if I supply a date of '2009-08-05', I want to find all the totals for ColumnA from 2009-01-01 to 2009-08-05 for ytd, 2009-08-01 to 2009-08-05 for month to date, and 2009-08-02 to 2009-08-05 for week to date.

I've been messing around with DATE_SUB, etc., but I keep getting errors regarding the necessity of a group by clause?

Anyone know how I can do this?

[kiwi2]

Dates r so much fun LOL, look into a BETWEEN clause

$q = mysql_query("SELECT field, COUNT(field) as total, field FROM table WHERE afileld BETWEEN '$date AND '$nextdate' GROUP BY fileldORDER BY total");
while ($row = mysql_fetch_assoc($q) )
{
ect;
}
?>