db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: how to count total number of records in each group , when using group by statement.
Date Mon, 28 Nov 2005 09:37:44 GMT
Answer to both of Legolas' questions:

Consider table x with a timestamp field  like this:

ij> select * from x;
D                         
--------------------------
2005-11-28 10:26:33.0     
2005-11-28 10:26:33.0     
2005-11-28 11:26:33.0     
2005-11-28 13:26:33.0     
2005-11-28 14:26:33.0     
2005-11-28 14:00:33.0     
2005-11-28 14:01:33.0     

To count the number of timestamps and group them by the hour:

ij> select h,count(*) from (select hour(d) from x) as t(h) group by h;
H          |2          
-----------------------
10         |2          
11         |1          
13         |1          
14         |3          

To extract the year, month, day etc. from table x:

ij> select year(d),month(d),day(d),hour(d),minute(d),second(d) from x;
1          |2          |3          |4          |5          |6                     
----------------------------------------------------------------------------------
2005       |11         |28         |10         |26         |33.0                  
2005       |11         |28         |10         |26         |33.0                  
2005       |11         |28         |11         |26         |33.0                  
2005       |11         |28         |13         |26         |33.0                  
2005       |11         |28         |14         |26         |33.0                  
2005       |11         |28         |14         |0          |33.0                  
2005       |11         |28         |14         |1          |33.0                  


>>>>>>>>>>>> Legolas Woodland wrote (2005-11-27 23:44:24):
> Hi
> Thank you for reading my post.
> is there any way to count total records in each group of records when 
> using "group by" command ?
> for example i need to find how much people log in each hour of a day.
> in this case i can use group by to group the record based on hour field 
> and then use count to find how much people log in in each hour out of 24 
> :-) .
> please tell me if my design is not correct.
> 
> BTW :
> I get one field for each element of Data time , is it a good design ? i 
> have these fields for year , Month , Day , Hour , minutes , seconds  . 
> is it good or i can use a dateTime field and sql let me to access each 
> element in datetime field ?)
> 
> 

>>>>>>>>>>>> Legolas Woodland wrote (2005-11-28 12:42:07):
> Hi
> Thank you for reply.
> i get one field for each of a DATETIME element in my table.
> it means that i have : year , month, day , hour, minute, second .
> i do this to be able to use each element in my queries. i find that in 
> Oracle it is possible to extract each of those elements in sql commands 
> and use them.
> now my question is how i can do the same (using a datetime and then 
> access to each of the element) in derby ?
> 


-- 
Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway

Mime
View raw message