db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Legolas Woodland <legola...@gmail.com>
Subject Re: how to count total number of records in each group , when using group by statement.
Date Mon, 28 Nov 2005 10:10:53 GMT
Bernt M. Johnsen wrote:
> 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 ?
>>
>>     
>
>
>   
Great help . honestly you ease my work too much.
Thank you very much.

Mime
View raw message