SQLTeam.com | Weblogs | Forums

Simple Group By Clause Question!


#1

Hi guys,

I have a simple group by clause question I can't seem to figure out.

I have a table called Students and a column in that table called StartDate.

I want to return the number students who enrolled in each calendar month over the years to see which months produce the most enrollments.

January 95
February 136
March 72
etc.

The statement below is what I've got but of course this isn't returning the result set I want.... what am I missing?

SELECT COUNT(StudentID) AS Enrolments, DATENAME(Month, StartDate) AS StartMonth
FROM STUDENTS
GROUP BY StartDate

Thanks!


#2

please ...change

group by startdate
to
group by DATENAME(Month, StartDate)

hope it helps
:slight_smile:
:slight_smile:


#3

Personally I'd do the COUNTs using month# and translate to month name only for the actual display. That way you can sort by month# to see the results as Jan, Feb, Mar, ...

SELECT Enrolments, DATENAME(MONTH, DATEADD(MONTH, StartMonth - 1, 0)) AS Start_Month
FROM (
    SELECT COUNT(StudentID) AS Enrolments, MONTH(StartDate) AS StartMonth
    FROM STUDENTS
    GROUP BY MONTH(StartDate)
) AS derived
ORDER BY StartMonth