• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
raminaa niilian
Ranch Hand
Posts: 551
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?)
 
Mahesh Rana
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use of different fields for Year, Month, Day.... is going to make your life miserable.

Right (and only?) way is to use the Date/Time type of that RDBMS.

Assuming that you are using Oracle, the query for your "GROUP BY" hour should look something like this:

select count( to_char(date_field, 'HH')), to_char(date_field, 'HH')) from
your_table GROUP BY to_char(date_field, 'HH'))
 
raminaa niilian
Ranch Hand
Posts: 551
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Thank you for reply.
Does your sql statement return each group count ?
for eample we have 100,000 record in ten group when we use group by
in our select statement.
now i want to find each group count , does your sql statement do this ?
is it applicable in derby ?



Thank you again
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Possibly you are looking for Rollup and Cube function


Shailesh
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic