count(*) by hour

#1

Hi all
I have this query
SELECT COUNT(*)
FROM Pro
WHERE D_TIME BETWEEN '2010-02-14 00:00:00.000' AND '2010-02-20
23:59:59.000'
AND convert(varchar(30),D_TIME,114) BETWEEN '00:00:00.000' AND
'00:59:59.000'
which gives me the total count for 00:00-00:59 from 02-14-10 thru
02-20-10.
i need to get for all 24 hours in 1 statment.

Time Total
00:00-00:59
01:00-01:59
02:00-02:59
......
23:00-23:59

Thank you

#2

On Thu, 25 Feb 2010 12:51:11 -0800 (PST), hayko98
<vardan.hakopian (AT) gmail (DOT) com> wrote:

Quote:

Hi all
I have this query
SELECT COUNT(*)
FROM Pro
WHERE D_TIME BETWEEN '2010-02-14 00:00:00.000' AND '2010-02-20
23:59:59.000'
AND convert(varchar(30),D_TIME,114) BETWEEN '00:00:00.000' AND
'00:59:59.000'
which gives me the total count for 00:00-00:59 from 02-14-10 thru
02-20-10.
i need to get for all 24 hours in 1 statment.

Time Total
00:00-00:59
01:00-01:59
02:00-02:59
.....
23:00-23:59

Thank you

Give this a try:

SELECT DATEPART(hh, D_TIME), COUNT(*)
FROM Pro
WHERE D_TIME BETWEEN '2010-02-14 00:00:00.000' AND '2010-02-20
23:59:59.000'
GROUP BY DATEPART(hh, D_TIME)

This should give you one row for each hour in your query.

--
David

#3

On Feb 25, 5:27*pm, David Solimano <da... (AT) solimano (DOT) org> wrote:

Quote:

On Thu, 25 Feb 2010 12:51:11 -0800 (PST), hayko98

vardan.hakop... (AT) gmail (DOT) com> wrote:
Hi all
I have this query
SELECT COUNT(*)
FROM * Pro
WHERE * * *D_TIME BETWEEN '2010-02-14 00:00:00.000' AND '2010-02-20
23:59:59.000'
* * * AND convert(varchar(30),D_TIME,114) BETWEEN '00:00:00.000' AND
'00:59:59.000'
which gives me the total count for 00:00-00:59 from 02-14-10 thru
02-20-10.
i need to get for all *24 hours in 1 statment.

Time * * * * * * * Total
00:00-00:59
01:00-01:59
02:00-02:59
.....
23:00-23:59

Thank you

Give this a try:

SELECT DATEPART(hh, D_TIME), COUNT(*)
FROM * Pro
WHERE * * *D_TIME BETWEEN '2010-02-14 00:00:00.000' AND '2010-02-20
23:59:59.000'
GROUP BY DATEPART(hh, D_TIME)

This should give you one row for each hour in your query.

--
David- Hide quoted text -

- Show quoted text -

Thank you.
It worked