• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 551
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ?)
 
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Possibly you are looking for Rollup and Cube function


Shailesh
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic