OK, now I think I understand your question. The answer, in that
case, is a little more complicated.
SELECT Sum(1) AS monthcount
DateSerial(Year([mydate]),Month([mydate]),1) AS monthbeg
FROM mytable
WHERE mytable.mydate Is Not Null
GROUP BY DateSerial(Year([mydate]),Month([mydate]),1)
ORDER BY DateSerial(Year([mydate]),Month([mydate]),1)
The expression DateSerial(Year([mydate]),Month([mydate]),1)
builds a date value using the year and month from
a date field named mydate, plus day 1. Thus,
8:00 AM on June 2, 2004 and
10:00 PM on June 30, 2004 both come out
midnight on June 1, 2004.
The WHERE clause excludes record where the mydate field has no
value. Otherwise, any such records would blow up the DateSerial
function.
The GROUP BY clause tells the DBMS to combine all records having
the same DateSerial(... ) value into one record.
The Sum(1) as monthcount clause adds one to a counter for each
record in a DateSerial group, and presents the resulting value in
a calculated field named monthcount.
The ORDER BY clause tells the DBMS to return the records in DateSerial
sequence.
Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
Hey,
So would I be able to do this for all 12 months in the
same sql statement? Or would i have to make 12 different
statements?
Thanks,
Matt
-----Original Message-----
Howdy.
No, it would look like:
SELECT Count(*) AS mycount
FROM mytable
WHERE Month([mydate])=1
where mydate is the name of a date column in your database
and 1 means January.
Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
-----Original Message-----
Hey,
I want a count of each month entered into my database (how
many jan's, feb's, etc.). What would the correct syntax
be to do this? Would it look something like this?
Count(Month=1) AS Jan
Count(Month=2) AS Feb
Thanks,
Matt
.
.
