Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: Group by 1/2 hour

    If I have a table with columns like this:

    userid (number)
    logintime (date)

    and I want to create a query which shows by the 1/2 hour for today, the number of userids in each 1/2 hour bucket, how is that possible? I've been trying approaches for the last two days with no luck.

    something like
    select count(userid) as COUNT,trunc(logintime,XXXXX) as BUCKET where sysdate = today;

    so I would get

    COUNT BUCKET
    25 7:30
    50 8:00
    43 8:30

    etc.

    ANY HELP IS GREATLY APPRECIATED!!!

    RON

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The solution will likely contain the MOD function
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Posts
    3
    Unfortunately, that really doesn't help very much.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  select to_char(date_created,'HH24')||':'|| ltrim(to_char(floor(((to_number(to_char(date_created,'MI')))/30))*30,'09')) , count(*)
      2  from line_item where date_created > trunc(sysdate)
      3  group by to_char(date_created,'HH24')||':'|| ltrim(to_char(floor(((to_number(to_char(date_created,'MI')))/30))*30,'09'))
      4* order by 1
    11:37:10 SQL> /
    
    TO_CHA   COUNT(*)
    ------ ----------
    00:00         265
    00:30         187
    01:00         141
    01:30         124
    02:00          85
    02:30          98
    03:00          77
    03:30          83
    04:00          58
    04:30          56
    05:00         103
    05:30         119
    06:00         160
    06:30         234
    07:00         288
    07:30         403
    08:00         537
    08:30         568
    09:00         692
    09:30         786
    10:00         795
    10:30         777
    11:00         755
    11:30         844
    12:00         851
    12:30         824
    13:00         886
    13:30         218
    
    28 rows selected.
    
    11:37:18 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2007
    Posts
    3
    Sheer genius!!! That worked perfectly. HUMBLE BOW IN YOUR DIRECTION.

    RON

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You are welcome.
    I needed the challenge.
    Do you understand how/why it produces the desired results?
    Part of the "magic" is two aspects of basic arithmetic.
    1) 1 * constant = constant
    2) 0 * anything = 0
    I did NOT check for correctness WRT "boundary conditions"; i.e. where actual minute is either 00 or 30.
    If you produce a small sample of test data, you can easily verify whether or not the results are 100% correct.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •