Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Counting Group By of Name AND Day From DateTime

Author  Topic 

trollersteve
Starting Member

5 Posts

Posted - 2013-12-06 : 13:23:23
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-06 : 13:33:00
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 - 2013-12-06 : 13:57:49
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

52326 Posts

Posted - 2013-12-07 : 08:25:50
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
   

- Advertisement -