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

    Unanswered: Calculations in Oracle

    I have a table called

    user which has user_id, customertype and date_created.

    I need to find out the '% of change in the # of users registered' over the previous month.

    So, Is there anyway, that this % can be calculated in a Query?

    Thanks,

  2. #2
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    yes

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    YES, you'll get different answers depending upon
    whether you use last month's total or this month's total
    as the basis for the resultant % change calculation.
    The solution is SMOP; a Small Matter Of Programming.

  4. #4
    Join Date
    Feb 2004
    Posts
    35
    I can definitely program it in the application, but because I am new to any such calculations in Oracle, I am interested in knowing how I would do that..

    Basically, the formula is

    Step1:
    Select count(user_id) as Curr_Month from up_user where date_created between '1-JAN-2004' AND '31-JAN-2004'

    Step2:
    Select count(user_id) as Prev_Month from up_user where date_created between '1-DEC-2003' AND '31-DEC-2003'

    Step3:
    result (in%) = (count(Curr_Month) - count(Prev_Month))/count(Curr_Month) * 100


    Any help in writing a query for this would be appreciated.

    Thanks,

  5. #5
    Join Date
    Mar 2004
    Posts
    14
    sorry , I don't check it, but if you neet do this only by query so something like this can help

    select b.cnt_/a.cnt_ * 100, a.month_ , a.customertype from

    ( select customertype , trunc( date_created,'mm') month_, count ( * ) cnt_
    group by trunc( date_created,'mm') , customertype ) month_a

    ( select customertype , trunc( add_month(date_created,1),'mm') month_, count ( * ) cnt_
    group by trunc( date_created,'mm') , customertype ) previous_b

    where a.month_ = b.month_(+)
    and a. customertype = b.customertype


    but I think too that at least view or function will work quikly

Posting Permissions

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