home bbs files messages ]

Forums before death by AOL, social media and spammers... "We can't have nice things"

   comp.databases.ms-sqlserver      Notorious Rube Goldberg contraption      19,505 messages   

[   << oldest   |   < older   |   list   |   newer >   |   newest >>   ]

   Message 17,765 of 19,505   
   David Solimano to vardan.hakopian@gmail.com   
   Re: count(*) by hour   
   25 Feb 10 20:27:26   
   
   9b11b8c7   
   From: david@solimano.org   
      
   On Thu, 25 Feb 2010 12:51:11 -0800 (PST), hayko98   
    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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)   

[   << oldest   |   < older   |   list   |   newer >   |   newest >>   ]


(c) 1994,  bbs@darkrealms.ca