Group by datetime

Hi,
How can i group the datetime field with different time period in same day
e.g 2006/1/23 07:00:00 12
2006/1/23 07:01:00 10
result : 2006/1/23 22
Thanks & Best Regards,
SexballYou would need to chop the time portion off...
SELECT CAST(CONVERT(char, YourDateCol, 112) AS datetime), SUM(YourInt)
FROM TableName
GROUP BY CAST(CONVERT(char, YourDateCol, 112) AS datetime)
HTH. Ryan
"sexball" <sexball@.sexball.com> wrote in message
news:Ohfnc6AIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How can i group the datetime field with different time period in same day
> e.g 2006/1/23 07:00:00 12
> 2006/1/23 07:01:00 10
> result : 2006/1/23 22
>
> Thanks & Best Regards,
> Sexball
>|||Hi
Just try this:
select <date-field>, count(*)
from <table>
group by convert(varchar(10), <date-field>, 101)
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"sexball" wrote:

> Hi,
> How can i group the datetime field with different time period in same day
> e.g 2006/1/23 07:00:00 12
> 2006/1/23 07:01:00 10
> result : 2006/1/23 22
>
> Thanks & Best Regards,
> Sexball
>
>|||The result will be the error .. "<date-field> is invalid in the SELECT list
as it's not part of the GROUP BY"...
You'll need to say :-
select convert(varchar(10), <date-field>, 101), count(*)
from <table>
group by convert(varchar(10), <date-field>, 101)
But this will return a count of the rows returned not the SUM value the
poster was after.
select convert(varchar(10), <date-field>, 101), SUM(intvalue)
from <table>
group by convert(varchar(10), <date-field>, 101)
HTH. Ryan
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:5744C110-1B28-440B-A17A-F4B14B026F72@.microsoft.com...
> Hi
> Just try this:
> select <date-field>, count(*)
> from <table>
> group by convert(varchar(10), <date-field>, 101)
> Please let me know if you have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "sexball" wrote:
>|||Try this,
CREATE TABLE #TEST(id1 int, date1 datetime)
INSERT INTO #TEST VALUES (1,GETDATE())
WAITFOR DELAY '00:00:02'
INSERT INTO #TEST VALUES (2,GETDATE())
WAITFOR DELAY '00:00:02'
INSERT INTO #TEST VALUES (3,GETDATE())
select * from #TEST
SELECT CAST(FLOOR(CAST( date1 AS float)) AS DATETIME),SUM(id1) FROM #TEST
GROUP BY
CAST(FLOOR(CAST( date1 AS float)) AS DATETIME)
HAVING COUNT(date1) > 1
DROP TABLE #TEST
Thanks,
Sree
"sexball" wrote:

> Hi,
> How can i group the datetime field with different time period in same day
> e.g 2006/1/23 07:00:00 12
> 2006/1/23 07:01:00 10
> result : 2006/1/23 22
>
> Thanks & Best Regards,
> Sexball
>
>