Simple group by not working on datetime field?

Can someone help me out with this? I'm not sure why my group by isn't working.

SELECT [Date], SUM(counted) as TotalCount
FROM dbo.TableName
GROUP BY [Date], TotalCount
ORDER BY [Date]

result

Date                        TotalCount

2011-09-07 00:00:00.000 12 
2011-09-07 00:00:00.000 14 
2011-09-07 00:00:00.000 11

I'm wanting the result to look like the following

2011-09-07 00:00:00.000 37

Thanks

This MSSQLServer 2008, and I want to group by the date and 开发者_如何学Ctime.

Don't GROUP BY [Date], TotalCount instead you need GROUP BY [Date]

This would cause an error though unless you also had a column in your source of that name, e.g. as below.

;WITH TableName([Date], counted, TotalCount) as
(
select '2011-09-07 00:00:00.000',12,1 union all
select '2011-09-07 00:00:00.000',14,2 union all 
select '2011-09-07 00:00:00.000',11,3
)
SELECT [Date], SUM(counted) as TotalCount
FROM TableName
GROUP BY [Date], TotalCount
ORDER BY [Date]

It may be a problem with the time part of your field. If you want to group by only the date, then you can try the following:

SELECT CONVERT(VARCHAR(8),[Date],112) [Date], SUM(Counted) TotalCount
FROM dbo.TableName
GROUP BY CONVERT(VARCHAR(8),[Date],112)

To avoid the possibility of different values due to ticks, you might want to convert the Date into CHAR and do a group by.