[RESOLVED] grouping records

[wengang]

Hey all.
Sorry to be asking for a freebie, but I've been out of coding for a few years and now I can hardly remember anything.

I have a db with a tblAppts full of appointments.
All I want is the dates in a given month where there were appts and the number of appts on each of those days.

I know it is a simple query, but I forgot how to do it.

The resulting recordset would be:

Date NumofAppts
10/2/2010 7
10/5/2010 3
10/18/2010 9

etc

SQLSERver 2005

Sth like:

select Count(ID) as NumofAppts from tblAppts where Month(Appt) = '10' and Year(Appt) = '2010' Group by Day(Appt) Order by 1 Asc

Actually, i'm not sure it's that simple. Appt is actually a DateTime with YMD:HMS

Can anybody correct that for me?
Thanks

[si_the_geek]

If there were no time values (or all times for a day were the same), you could use this:

SELECT Appt, Count(ID) as NumofAppts 
FROM tblAppts 
WHERE ...
GROUP BY Appt
ORDER BY 1 Asc

Assuming that is not the case, you need to eliminate the time portion of the value, which you can do using Cast / Convert:

SELECT CONVERT(varchar(10), Appt, 110), Count(ID) as NumofAppts 
FROM tblAppts 
WHERE ...
GROUP BY CONVERT(varchar(10), Appt, 110)
ORDER BY 1 Asc

At this stage the first column will be a VarChar rather than a DateTime, if you want it to be a DateTime then you will need to use Cast/Convert around the Convert function above to change it.

[wengang]

oh yeah.
that's much smarter. And I never would have thought of it.
Thanks