Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Unanswered: Average Number of Registrations per day

    Hi,

    How can I get average number of users registered per day from a User table?

    The columns are:
    USER_ID
    CUSTOMERTYPE
    GTEHACCOUNT
    SUB_ORG_ID
    DATE_CREATED
    LAST_LOGIN
    LAST_ACTIVITY
    MESSAGE_NUM
    CUSTOMERSUBTYPE
    CONTRACTHOLDERNAME
    CONTRACTHOLDEREMAILADDRESS
    SALESREPEMAILADDRESS
    AVG_SHIP_MON
    WHAT_U_SHIP
    TITLE
    EMAIL_NOTIFY
    SSOUSER_ID
    SSOPASSWORD
    TNCFLAG
    TNCFLAGDATE
    NOTES

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select DATE_CREATED, Count(*)
    from User
    group by TRUNC(DATE_CREATED);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    R123456,

    I think you may ave a slight syntax error in your code, certainly under 8.x.x.x. anyway...

    select TRUNC( DATE_CREATED ), Count(*)
    from User
    group by TRUNC(DATE_CREATED);

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: Average Number of Registrations per day

    Originally posted by durbhakula
    Hi,

    How can I get average number of users registered per day from a User table?

    The columns are:
    USER_ID
    CUSTOMERTYPE
    GTEHACCOUNT
    SUB_ORG_ID
    DATE_CREATED
    LAST_LOGIN
    LAST_ACTIVITY
    MESSAGE_NUM
    CUSTOMERSUBTYPE
    CONTRACTHOLDERNAME
    CONTRACTHOLDEREMAILADDRESS
    SALESREPEMAILADDRESS
    AVG_SHIP_MON
    WHAT_U_SHIP
    TITLE
    EMAIL_NOTIFY
    SSOUSER_ID
    SSOPASSWORD
    TNCFLAG
    TNCFLAGDATE
    NOTES
    I guess it should be something like following.

    SELECT (NVL (v_total_users,0)/NVL (total_days,1)) "Average Users Registered a Day"
    FROM (SELECT SUM (total) v_total_users
    FROM (SELECT count (*) total
    FROM users
    GROUP BY TRUNC (DATE_CREATED)
    )
    ),
    (SELECT (a-b+1) total_days
    FROM (SELECT MAX (TRUNC (DATE_CREATED)) a, MIN (TRUNC
    (date_created)) b
    FROM users)
    );

    Or if the user_id is the primary key then,

    SELECT (NVL (v_total_users,0)/NVL (total_days,1)) "Average Users Registered a Day"
    FROM (SELECT COUNT (user_id) v_total_users
    FROM users
    ),
    (SELECT (a-b+1) total_days
    FROM (SELECT MAX (TRUNC (DATE_CREATED)) a, MIN (TRUNC (date_created)) b
    FROM users)
    );
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Feb 2004
    Posts
    35
    This is what my SQL*Plus is howing me..Of course, I can always split the SQL into 2 and get the results that I need..but, it's interesting to know the reason for the error

    I am executing:

    SQL> select (NVL(v_total_users,0)/NVL(total_days,1)) "Average Registered Users a Day" from (select count(user_id) v_total_users from up_user), select (a-b+1) total_days from (select MAX(TRUNC(DATE_CRE
    ATED)) a, MIN(TRUNC(DATE_CREATED)) b from up_user));

    The ERROR is :

    select (NVL(v_total_users,0)/NVL(total_days,1)) "Average Registered Users a Day" from (select count(

    ERROR at line 1:
    ORA-00903: invalid table name

    Any ideas??

  6. #6
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi, You did not type the exact script which I provided in my previous script. You missed the '(' in front of select (a+b+1).

    It should be -- (select (a+b+1) instead of

    -- select (a+b+1)

    so, corrected statement is like below.


    select (NVL(v_total_users,0)/NVL(total_days,1)) "Average Registered Users a Day" from (select count(user_id) v_total_users from up_user), (select (a-b+1) total_days from (select MAX(TRUNC(DATE_CRE
    ATED)) a, MIN(TRUNC(DATE_CREATED)) b from up_user));

    Thanks
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  7. #7
    Join Date
    Feb 2004
    Posts
    35
    OK..thanks..I have done the same SQL that you have given in another way. I figured couple of things..Oracle did not accept "double quotes" and also the ";" at the end of the query.

    I have one more question..

    How can I get the DAY on which "MAX NUMBER USERS REGISTERED"??

    Thanks,

  8. #8
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Originally posted by durbhakula
    I figured couple of things..Oracle did not accept "double quotes" and also the ";" at the end of the query.


    How can Oracle (sqlplus) accepts it on my m/c?
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  9. #9
    Join Date
    Feb 2004
    Posts
    35
    Do not know HINGS..Maybe some settings in my SQL*Plus environment..??

    Anyways..

    How can I get the DAY on which "MAX NUMBER USERS REGISTERED"??

  10. #10
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    SELECT TRUNC (date_created)
    FROM (SELECT date_created, count (*) total
    FROM table
    GROUP BY TRUNC(date_created)
    )
    WHERE total = (SELECT MAX (v_total)
    FROM (SELECT date, count (*) v_total
    FROM table
    GROUP BY TRUNC(date))
    );
    I have not tried it no my db. so it may contain some syntax error. Please corect it if it has any.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Sorry Hings, I'm getting well confused trying to follow your code. I had also missed durbhakula asking for average per day, not total per day in the original post.

    Does this not give an average per day over the entire table - and a lot simpler?

    PHP Code:
    select mintrunccreated_date )) as first_date,
           
    maxtrunccreated_date )) as last_date,
           
    count(*) as total_users,
           
    count(*) / (maxtrunccreated_date )) - mintrunccreated_date ))) as average_users_per_day                      
    from table 
    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  12. #12
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi Bill,

    Yes, your code is right too.

    But needs litle change. You have to add 1 in the diference of dates ( max and min dates) to get the correct result.

    Thanks,
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  13. #13
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Is there any issue using AVG function provided by Oracle??

    I guess
    PHP Code:
    select avg(num_registeredfrom
    (select trunc(date_created), count(*) num_registered from users group by trunc(date_created)) 
    will also do what you require.
    Oracle can do wonders !

  14. #14
    Join Date
    Feb 2004
    Posts
    35
    Infact, Hings pointed me in the right direction (more or lesss the solution) and I had done some tweaking to it based on the SQL*Plus environment that I have..

    But, yeah basically, I have done the query both ways (Hings and Bill)

    Sharma,

    No Issues with 'AVG' function. But, more to do with my question ..and ofcourse as you might agree, "a question can be tackled in different ways".

    Thanks Sharma.

  15. #15
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Originally posted by cmasharma
    Is there any issue using AVG function provided by Oracle??

    I guess
    PHP Code:
    select avg(num_registeredfrom
    (select trunc(date_created), count(*) num_registered from users group by trunc(date_created)) 
    will also do what you require.
    I do not think So.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

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