var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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?
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.
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
Select count(user_id) as Curr_Month from up_user where date_created between '1-JAN-2004' AND '31-JAN-2004'
Select count(user_id) as Prev_Month from up_user where date_created between '1-DEC-2003' AND '31-DEC-2003'
result (in%) = (count(Curr_Month) - count(Prev_Month))/count(Curr_Month) * 100
Any help in writing a query for this would be appreciated.
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