Monthly count

[zzz]

I have two tables:

t1 and t2

t1 has the following fields: t1.itemID, t1.issueID, t1.userID

t2 has the following fields: t2.issueID, t2.issueDate

Also t1.issueID == t2.issueID

What I need is to count by month, how many times t1.itemID appears per month for each t1.userID.

I guess I could do something like:

$january = $db->get_var("SELECT count(t1.itemID) FROM t1
                        LEFT JOIN t2 ON (t1.issueID = t2.issueID) 
                        WHERE t2.issueDate = '01' ");

or something similar, but it just doesn't sound right to do that way twelve times...

Any suggestions on how should I tackle this one?

Also the t2.issueDate field type is data and has the following format: 0000-00-00 (eg. 2005-02-15). I guess I need date function to get the month, right?

[Roger_Ramjet]

Yes, you need the Month() function, and probably the Year() function as well - unless its irrelavent what year it is.
You also need to use GROUP BY when you want to use an aggregate function like Count()

$sql = "SELECT t1.userID as u, count(t1.itemID), YEAR(t2.issueDate) as y, MONTH(t2.issueDate) as m FROM t1
                        JOIN t2 ON (t1.issueID = t2.issueID)
                        GROUP BY u, y, m,  t1.itemID "

should just about do it

No it won't, not like that cos the null dates are not going to give you the zero counts you want when there is no entry. Just changed it to a normal join, so you will have gaps in the date sequence.