Group By Month SQL Query

I have the below query which shows totals for the month. Question is how do I show the month in a fourth column with the actual month. (Saying like "January" for example). Any help would be greatly appreciated.


SELECT     COUNT(studyTbl.studyid_i) as study_count, companyTbl.companyname_vc
FROM         physicianTbl INNER JOIN
                      companyTbl ON physicianTbl.companyid_i = companyTbl.companyid_i INNER JOIN
                      studyTbl ON physicianTbl.physicianid_i = studyTbl.primarycarephysicianid_i
 GROUP BY Month(studydt_dt) , companyTbl.companyname_vc

Open in new window

jazzcatoneAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ephraim WangoyaConnect With a Mentor Commented:
try
SELECT     COUNT(studyTbl.studyid_i) as study_count, companyTbl.companyname_vc, DATENAME(MONTH, studydt_dt) [Month]
FROM         physicianTbl INNER JOIN
                      companyTbl ON physicianTbl.companyid_i = companyTbl.companyid_i INNER JOIN
                      studyTbl ON physicianTbl.physicianid_i = studyTbl.primarycarephysicianid_i
 GROUP BY Month(studydt_dt) , companyTbl.companyname_vc

Open in new window

0
 
CboudrozConnect With a Mentor Commented:
ewangoya:

add a MIN() on the studydt_dt if you want to make it work.

or group on DATENAME(MONTH, studydt_dt) instead of grouping on Month(studydt_dt)


 
SELECT     COUNT(studyTbl.studyid_i) as study_count, companyTbl.companyname_vc, DATENAME(MONTH, studydt_dt) [Month]
FROM         physicianTbl INNER JOIN
                      companyTbl ON physicianTbl.companyid_i = companyTbl.companyid_i INNER JOIN
                      studyTbl ON physicianTbl.physicianid_i = studyTbl.primarycarephysicianid_i
 GROUP BY DATENAME(MONTH, studydt_dt) , companyTbl.companyname_vc

Open in new window



also make sure that you understand that if you have 2 year of data in you table, you will receive the count of January 2010 and January 2011 on the same row, normally you should group by "year and month".



0
 
Ephraim WangoyaConnect With a Mentor Commented:

@Cboudroz
Quite right, oversite on my part
0
 
jazzcatoneAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.