Re: how to count total number of records in each group , when using group by statement.

[Legolas Woodland]

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.