SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Counting Group By of Name AND Day From DateTime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

trollersteve
Starting Member

5 Posts

Posted - 12/06/2013 :  13:23:23  Show Profile  Reply with Quote
I have a table with two columns that I am using (AuthorName and CreateDT). CreateDT is in the format 2013-09-03 16:19:27.940. I need to count how many recordings each Author did each day for this year.

I tried the following code, but I get the error:
Column 'FM.dbo.WORKOBJ.CreateDT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

In my research I haven't even been able to tell for sure if that code would give me a grouping of each of 365 days or the day number of each month grouped together which I don't want.

Am I on the right track or do I need to go about this a different way?

Select CreateDT
,AuthorName
,count(*) as cnt 

FROM [FM].[dbo].[WORKOBJ]

Where CreateDT >= '2013-01-01' and CreateDT <= '2013-12-31' 

Group By day(CreateDT), AuthorName

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/06/2013 :  13:33:00  Show Profile  Reply with Quote
you need to add the DAY function to the select list to match your group by clause:
Select 
	DAY(CreateDT) AS CreateDay
	,AuthorName
	,count(*) as cnt 
FROM 
	[FM].[dbo].[WORKOBJ]
Where 
	CreateDT >= '2013-01-01' and CreateDT <= '2013-12-31' 
Group By 
	day(CreateDT), AuthorName
Go to Top of Page

trollersteve
Starting Member

5 Posts

Posted - 12/06/2013 :  13:57:49  Show Profile  Reply with Quote
Thanks for the fast reply! That did allow me to run the code without the error, however it was grouping the same day each month together. I tweaked it a bit with the following which seems to work correctly.

SELECT 
	AuthorName,
    DATENAME(mm, CreateDT) AS Month, 
	DATENAME(dd, CreateDT) AS Day, 
    DATENAME(yyyy, CreateDT) AS Year, 
    COUNT(*) AS Total 
FROM [FM].[dbo].[WORKOBJ] 
GROUP BY 
	AuthorName,
    DATENAME(mm, CreateDT), 
	DATENAME(dd, CreateDT),
    DATENAME(yyyy, CreateDT)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/07/2013 :  08:25:50  Show Profile  Reply with Quote
instead if grouping separately by month,day and year . you could simply do this

SELECT 
	AuthorName,
       DATEADD(mm,DATEDIFF(mm,0, CreateDT),0) AS MonthDate,
    COUNT(*) AS Total 
FROM [FM].[dbo].[WORKOBJ] 
GROUP BY 
	AuthorName,
    DATEADD(mm,DATEDIFF(mm,0, CreateDT),0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000