Get Percentage Calculations?
Jan 21, 2012
I am fairly new to Oracle SQL developer and I am having an issue that may very simple but I just cannot work it out logically for some reason.I am trying to calculate the percentage of sales each employee has made form the total of trades. I have been trying to use the COUNT function but it obviously splits the counts for each employee. I then tried a inline view but couldn't get it to work and also a sub-query and had an issue with "not a single group function" and then "not a GROUP BY expression".
how I can do this? I will also need to add the syntax to another inline view
View 1 Replies
ADVERTISEMENT
Jan 10, 2013
I have a set of records what looks similar like this
Caller Friend Call_type Start_time Duration
12345 54321 sms 1/JAN/2013 09:25:11 0
54321 12345 sms 1/JAN/2013 09:35:12 0
13579 97531 sms 1/JAN/2013 09:37:12 0
97531 13579 voice 1/JAN/2013 09:39:12 3:25
12345 54321 sms 1/JAN/2013 09:42:32 0
12345 54321 sms 1/JAN/2013 09:52:45 0
12345 11111 sms 1/JAN/2013 09:53:45 0
54321 12345 sms 1/JAN/2013 10:25:36 0
12345 54321 sms 1/JAN/2013 11:25:34 0
...
What i need to do is to go trough the record set and get output similar to this for every relationship
Caller Friend Call_no avg_sms_response_time
12345 54321 0 15.5min
54321 12345 0 0.5min
12345 11111 0 120.30min
...
The biggest problem at the moment I have is that I don't know how to go trough all record set having to read it in only once. And other is my calculations doesn't work and im not really sure why.
I have attached code as far as I have gotten.
View 7 Replies
View Related
Jun 17, 2013
I have a table which is updated with new records in each 15minutes from network. In order to get the accurate information I must do some arithmetic calculations on some variables.
For example, if the value for column A is 10 @9.45AM and 15 for @10AM the real result should be 15-10=5 for 10AM; because the values are cumulative so I need to subtract. Similar to this I have same operations for 9 more attributes in my table as well.
In order to handle this I used view and did necessary operations by using joining table like this (table name is Operations lets say and id is Primary Key)
create view ....
...
from Operations current, Operations prev
where current.datetime(+) = prev.datetime - 1 / 96
and current.id = prev.id
When I use this view, the simple select query takes about 15min since I have 25GB record for this table. What can I use instead of this join and solve cumulative values issue?
View 3 Replies
View Related
Jul 19, 2012
Version of the database you are using: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Version of Application Express: 4.1.1.00.23
I have a requirement that is driving me stark raving bonkers.
I have a form that a user fills out, it sets the Status to 'Open' and I capture the Date Created On in a hidden field. When someone goes back in to the Form and sets the Status to 'Closed' I capture the Date Closed On in a hidden field. I need to calculate how many hours that it took to close the issue and display it on the form.
I found some javascript that does this
<Code>
<script type="text/javascript">
var msecPerMinute = 1000 * 60;
document.write("msecPerMinute -" + msecPerMinute + "<br />")
var msecPerHour = msecPerMinute * 60;
[code].......
While I can get it to work from dreamweaver I cannot get it to work from APEX. Both of my date fields in my database are defined as:
Type: TIMESTAMP
Fractional Precision: 6
Time Zone: LOCAL TIME ZONE
View 17 Replies
View Related
Sep 20, 2011
i have a requirement to create a query which gets the same month from the previous year of the selected date .
Example : i select SEP-2011 ( as sysdate )
it will return the SEP-2010 as well .
here is the code which works fine with sysdate, dual :
SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;
in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)
am unable to apply the above code on it , i guess its the data type pf the field .
View 5 Replies
View Related
Jan 9, 2013
I am running APEX 4.0.2.I am having an issue where I have a page item that has a post calculation
My source used is "Always..."
My source type is "Database Column"
I have tried putting the code in the page item under "Post Calculation Computation", as a dynamic action, and as a Computation Process. It displays properly on the form no matter where I put the code, but when I select Apply Changes it is not updating the database. What am I doing wrong? I have provided a sample of the code, and yes the column name is in the database.
CASE
WHEN :P12_NUM_OF_BOOKED_DEALS>0 AND :P12_DATE_1ST_COMPLETE IS NULL THEN '(2) In Process'
ELSE '(D1) Open Draft'
END
View 2 Replies
View Related
Jun 21, 2010
I have a one more query
col1 col2
3-18083017013-Standard
3-18083225012-Significant
3-18082775913-Standard
3-18082426912-Significant
3-18082097112-Significant
3-18081539512-Significant
3-18081946712-Significant
3-18080523612-Significant
3-18076873112-Significant
3-18076872712-Significant
3-18080522412-Significant
3-18080064612-Significant
3-18070899912-Significant
3-18081155713-Standard
3-18071160013-Standard
3-18077564213-Standard
3-18079220312-Significant
3-18073201313-Standard
note:
3-Standard 240 min
2-Significant 120 min
1) i need to calculate the percentage value.
general form is :
total number of col1_values(count) in 120 min/count(col1) *100
conditions to calculate percentage:
1)need to ca
View 16 Replies
View Related
Mar 13, 2013
I want to calculate the running percentage. sample data is as below.
CREATE TABLE prod
(
code VARCHAR2(8),
name VARCHAR2(30),
stdate DATE,
itons NUMBER(7,3),
rtons NUMBER(7,3)
[code]....
Required Output is
CODE NAME STDATE ITONS RTONS %age
-------- ------------------------------ --------- ---------- ---------- -------
0-01-001 SEEDS 17-JUL-12 193.155 0
1-01-001 OIL 17-JUL-12 0 81.906 42.404
1-02-001 MEAL 17-JUL-12 0 104.304 54.000
1-03-001 DURT OIL 17-JUL-12 0 1.242 0.643
2-01-001 WASTE 17-JUL-12 0 5.703 2.953
[code]....
Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000
View 6 Replies
View Related
Mar 29, 2011
In this select I would like to get db time percentage for every sql_id as I can see in AWR report.How can I do this?
select
sql.sql_id ,
sql.executions_delta ,
round(sql.elapsed_time_delta/1000000) elapsed_sec,
round(sql.elapsed_time_delta/1000000)/sql.executions_delta elapsed_exec,
[code]...
View 4 Replies
View Related
Sep 26, 2012
I want to find the Pass rate for each term by adding up the grades.
PASS = HD/D/PASS FAIL = FAIL and Z means no mark has been recieved yet so i do not want to include it in the calculation.
CREATE TABLE DAN_GR
(ID VARCHAR2(8),
TERM VARCHAR2(8),
GRADE VARCHAR2(8),
SUBJECT VARCHAR2 (8),
STAT VARCHAR2 (8))
[Code]....
So term 1201 has 1 Pass 1 Fail and a Z (which is not counted) so pass rate is 50%
term 1202 has has 1 Pass only so the pass rate is 100%
term 1203 has 1 Fail so pass rate is 0%
term 1204 has 1 Pass and 2 Fails and i dont count the 3rd fail because the stat is 'APSENT' i only want to count the once where stat is 'PRESENT'
WANT:
COUNT TERM PASS RATE
3 1201 50%
1 1202 100%
1 1203 0%
3 1204 33%
I am using:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
View 4 Replies
View Related
Aug 10, 2012
In my table three column are there, structure_code, attribute_code and percentage. one project have many attribute_codes, each attribute code have percentage value. The total of percentage value for a project is 100.
data like follows
structure_code Attribute_code percentage
160025 2531 30
160025 2536 20
160025 2537 50
160025 2538
162061 1468 0
Now i need to select which attribute_Code have maximum percentage for each project(structure_code).
View 3 Replies
View Related
Jan 10, 2012
See the attached pdf the output of my report. I want to calculate the GP at the Total column.
My GP formula is
trunc((profit) / decode(ACTUAL,0,1,ACTUAL)*100,2).
I tried using Placeholder Column & Formula Column but it is not working. I am using grouping in INV_DATE and summing all the columns.
View 22 Replies
View Related
Mar 13, 2011
Is it possible to grant a user a percentage amount of a specified tablespace ? for instance granting a user called userx 5% of USERS tablespace. How can I do that ?
View 3 Replies
View Related
Feb 24, 2013
look at these tables (Customer and Rates), having some sample data. I've created a function for returning the interest percentage for a particular customer based upon his/her investment start date and end date. I'm getting the desired result but I'm wondering if I need calculate the composite interest here...then how can I do that? Suppose a customer has invested some amount for 45 days, then I need to calculate the interest for initial 30 days as 5% and additional 15 days as 6% and I need to give the final amount. In this case how can I pull this task off? how can we do this interest calculation by an Oracle program?
Customer Table
CUSTOMER_ID AMOUNT START_DATE END_DATE
1000 40000 15-JAN-13 15-FEB-13
1001 34000 15-DEC-12 15-FEB-13
1002 35678 15-NOV-12 15-FEB-13
Rates Table
RATE_PCT LOW_TENURE HIGH_TENURE
5 0 30
6 0 60
7 0 90CREATE OR REPLACE FUNCTION
F_INTEREST_PCT (V_CUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE)
RETURN NUMBER AS V_RATE_PCT NUMBER;
[code]...
View 5 Replies
View Related
May 11, 2011
estimate the percentage of memory should i can allocate from the total physical memory (it is 6G) as attached in the image file as a print screen.
View 4 Replies
View Related
Jun 23, 2010
SL_NoStatus
191502Implemented
191690Implemented
190998Implemented
191346Implemented
190541Implemented
[Code]..
my above table consists of two columnc sl_no and Status,col1 indicates the process no and the status indicates it is implemented or cancelled or failed during implementation.
i need to find the percentage of the implemented+cancelled process over failed..
test case:
lets consider,
A->count(Sl_NO)
B->Count(STATUS) where STATUS='Implemented'
[Code]..
i think i have satisfactorily given enough data.. make it out using sql query..
View 3 Replies
View Related
Feb 12, 2012
I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups.
CREATE TABLE target_data
(
T_LINK VARCHAR2(50)
, t_mon varchar2(6)
, t_grp varchar2(30)
, t_views NUMBER
[code]...
When I run this, I get output as
t_grp count_80 goal
grp3 1 0.1
But I want the output as
t_grp count_80 goal
grp3 1 0.1
grp2 1 0.1
grp1 ... ...
grp0 ... ...
View 16 Replies
View Related
Jan 17, 2012
I have two values:
'AA:10%:1'
'AC:P35:20%'
I need to extract the numerical value preceding the '%' sign. for first row 10 should be extracted and for the second 20.
View 2 Replies
View Related
Jul 14, 2013
I've gotten it to display the percentage of markup correctly(as in, multiplied by 100 with no decimal places), but I cannot for the life of me get the percentage sign to display on the right of the values(i.e. 40, but not 40%).
Here's my code so far:
SELECT title, to_char(round((retail-cost)/retail*100), '99')
AS "MARKUP %" FROM BOOKS;
I've tried concatenating with both the + sign and the ampersand (&) sign, to no avail.
View 1 Replies
View Related
Jul 5, 2010
value of "Shared Pool Free %" in v$sysmetric is larger than 100%
SQL> select value, group_id from v$sysmetric where metric_name = 'Shared Pool Free %';
VALUE GROUP_ID
186.45107 2
186.4685382 3
SQL> show parameter shared_pool
NAME TYPE VALUE
-------------- ------------ --
shared_pool_reserved_size big integer 832149913
shared_pool_size big integer 0
SQL> show sga
Total System Global Area 1.0155E+11 bytes
Fixed Size 2163880 bytes
Variable Size 8.1068E+10 bytes
Database Buffers 2.0401E+10 bytes
Redo Buffers 79310848 bytes
View 2 Replies
View Related
Jun 20, 2013
Oracle Express 4.0 I have set up a Tabular Form. It has 5 columns. I wanted to set 4 of the columns to a width of 15%. 1 column to 40%. I set the appropriate values to style=width: "15%"; and style=width: "40%"; in Column Edit's Element Attributes. But all columns are displayed equally. If I try the format style="width: 40%" I get what appears to be 40% of the cell width for the text field (not the table width). Can %'s be used on Ellement Attributes?
View 0 Replies
View Related
May 2, 2012
How can we check completion status for running sql query. i.e. how much % completed
SQL> begin
2 delete from gsmcrmdw.wc_loy_txn_f_aa
3 where integration_id in
4 ( select integration_id
5 from support_olap.recover_wc_loy_txn_f_953to955
6 );
7 commit;
8 end;
9 /
View 35 Replies
View Related
Aug 21, 2012
couple of questions about below parameters, do they measure single processor or avg usage across all processorss and cpus for example will i recieve alert if CPU system time goes above 95% for single processors? or not
CPU Idle Time (%)
CPU System Time (%)
CPU User Time (%)
View 1 Replies
View Related
