You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Legolas Woodland <le...@gmail.com> on 2005/11/27 21:14:24 UTC
how to count total number of records in each group , when using group
by statement.
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 ?)
Re: how to count total number of records in each group , when using
group by statement.
Posted by Legolas Woodland <le...@gmail.com>.
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.
Re: how to count total number of records in each group ,
when using group by statement.
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Correction: Since year(), month() etc is not standard SQL, but scalar
functions defined in JDBC/ODBC escape syntax, the portable syntax for this would be:
instead of
> ij> select h,count(*) from (select hour(d) from x) as t(h) group by h;
write:
select h,count(*) from (select {fn hour(d)} from x) as t(h) group by h;
and instead of
> ij> select year(d),month(d),day(d),hour(d),minute(d),second(d) from x;
write:
select {fn year(d)},{fn month(d)},{fn day(d)},{fn hour(d)},{fn minute(d)},{fn second(d)} from x;
--------------------
Furthermore: year(), month etc in Derby returns 0 if the datetime
values is NULL, so if the column contains NULL values the first query
is wrong and should be
select h,count(*) from (select {fn hour(d)} from x where d is not null) as t(h) group by h;
However: if hour(d) is to behave like standard SQL extract(hour from
d), then hour(d) should be NULL when d is null. I think there is a bug
in Derby here. Anyway, if hour(d) *had* returned NULL, the proper
query would have been
select h,count(h) from (select {fn hour(d)} from x) as t(h) group by h;
since count(h) should ignore NULL values while count(*) should not.
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
Re: how to count total number of records in each group ,
when using group by statement.
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
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