PL/SQL :: Calculate Standard Deviation For Some Rows With STDDEV Function

Oct 10, 2012

I can easily calculate the standard deviation for some rows with the STDDEV function. But that returns me a value expressed in the same units as the things being measured. (In other words, if I have times of 1,12,6,11 and 8 seconds, I will be told that the average time is 7.6 seconds, and the standard deviation is 4.39 seconds).

What I'd like to be able to do is to say that the record with value 1 is (say) 3 std deviations away from the average. Or that the 6 second row is within 1 standard deviation.

I am trying to find records which are more than 3 standard deviations of the mean, because they are the outliers I am interested in.

I cannot see how to convert a STDDEV result into being a "number of standard deviations".

(If I have not expressed myself clearly, I'm using this sort of stuff URL....and I'm trying to find >3-sigma records)

simply to multiply the STDDEV result by 1, 2, 3 and so on, and then compare to the time? That is, is it OK to do the following:

Mean = 7.6 seconds (from my sample data mentioned in the above post)
StdDev = 4.29 seconds

So 2sigma would be 4.29*2 = 8.78 seconds, plus or minus the mean?

So any record with a time value between -1.18 (7.6-8.78) and 16.38 (7.6+8.78) seconds would be within 2-sigma, assuming normal distribution?

I am still wondering if there's a nice function or something that returns the sigma value for any given set of records?

View 5 Replies


ADVERTISEMENT

PL/SQL :: Average / Standard Deviation Of Multi-Column Record

Jun 28, 2012

I want to read a record that has 6 columns

RECORD     |Col1     |Col2     |Col3      |Col4     |Average      |Standard Deviation     
0001____|Null_|5___|8___|10__|8.75___|2.986079

With SQL I want to calculate Average and Standard Deviation.

View 4 Replies View Related

SQL & PL/SQL :: Function To Calculate The Vacation?

Oct 31, 2011

how can create function to calculate the vacation example if i takes 10 days vacation and Friday , Saturday only a normal vacation but The rest of the days is Within the vacation

how can calculate the vacation and make this function dynamic such as when takes the vacation 1-1-2011 and number of days vacation 10 not including the Friday, Saturday days

View 4 Replies View Related

SQL & PL/SQL :: PMT Function Which Calculate Payment

Dec 14, 2010

PMT function which calculates the payment for a loan based on constant payments and a constant interest rate. I need to build PMT function in oracle.

View 2 Replies View Related

SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary

May 8, 2010

I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?

Actual Output:::

DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600

I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column

View 4 Replies View Related

Calculate Working Days With Oracle Function

Mar 13, 2009

how do i can make oracle function for the following vb6 function.

This function is used for calculating working days between two date.

****************************************
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does not account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

On Error GoTo Err_Work_Days
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
[code]......

View 3 Replies View Related

SQL & PL/SQL :: Write A Function To Calculate Difference Between Two Dates?

Sep 22, 2011

create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;

that i have worte..

View 3 Replies View Related

SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.

View 7 Replies View Related

SQL & PL/SQL :: Pipeline Function - No Rows Returned

Aug 11, 2011

I need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.

SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.

Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.

CREATE TYPE r_report_mth_rec_obj AS OBJECT (
acct_num VARCHAR2 (20),
acct_name VARCHAR2 (80),
fund_group VARCHAR2 (80),
fund_type VARCHAR2 (80),
share_class_code VARCHAR2 (10),
share_class_description VARCHAR2 (20),
curr_code VARCHAR2 (10),
[code]...

View 16 Replies View Related

SQL & PL/SQL :: Function Return Multiple Rows

Jun 23, 2006

can i have a pl/sql function that can return multiple rows

may be the syntax will be like

create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;

..code to return the result of above statement to calling program..

end;

and functions will be called as

select candidateid from .. where candidateid in( select multiple());

View 13 Replies View Related

PL/SQL :: Function That Return Multiple Rows

Jun 6, 2013

I need a function that should return output of this query

SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;

This above function return 30 rows and I am not giving any input

Function using cursor,pipeline

View 5 Replies View Related

SQL & PL/SQL :: Rewrite Query Which Is In ANSI 92 Standard To ANSI 89 Standard

Jun 17, 2011

To rewrite this sql which is in ANSI 92 standard to ANSI 89 standard.

SELECT "PROJECT"."X_SAMPLED_DATE", SAMPLE"."SAMPLE_NUMBER"
FROM "SHIRE_PRD"."LimsUser"."SAMPLE" "SAMPLE" INNER JOIN "SHIRE_PRD"."LimsUser"."PROJECT" "PROJECT" ON"SAMPLE"."PROJECT"="PROJECT"."NAME"
WHERE ("SAMPLE"."SAMPLE_TYPE"='EM' OR "SAMPLE"."SAMPLE_TYPE"='WATER') AND "SAMPLE"."STATUS"<>'X' AND("PROJECT"."X_SAMPLED_DATE">={ts '2011-05-01 00:00:00'} AND "PROJECT"."X_SAMPLED_DATE"<{ts '2011-06-01 00:00:00'})
ORDER BY "SAMPLE"."PRODUCT"

View 4 Replies View Related

Listagg Function In 11g For Concatenating Values From Different Rows

Sep 11, 2013

I'm Trying to use Listagg function in oracle 11g for concatenating values from different rows,but i'm getting error as FROM KEYWORD NOT FOUND.

Query is:
select listagg(column_name,'') within group (order by column_name) "column_name"
from table_name;

View 2 Replies View Related

SQL & PL/SQL :: PIPE Multiple Rows At Once In PIPELINED Function?

Sep 13, 2012

I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.

Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.

Below is the test case:

CREATE TABLE TMP_EMP
(
EMP_ID NUMBER(10,0),
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER(10,0),
SALARY NUMBER(14,0),

[code]....

View 13 Replies View Related

Move From Standard To Enterprise 11g

Mar 9, 2012

i want to move from 11g Standard to 11g Enterprise (same Version) on Windows.

View 2 Replies View Related

Using Oracle XDB In 10.2.0.3.0 Standard Edition

Feb 20, 2013

I am using Oracle 10.2.0.3.0 standard edition. Now i want to use Oracle XDB in the same.

Can i use this directly or does this require license.

View 2 Replies View Related

Enterprise To Standard Edition

Sep 12, 2012

Recently we have downgraded our database from enterprise to standard edition.....our sga size before downgrade was 11 gb and now it is 11gb and there is no as such problem in database..I have read somewhere that standard edition doesn't support sga size more than 2 gb .

View 1 Replies View Related

Standard Configuration That Uses Built-in Apache

Jul 1, 2010

This error occurs while trying to print a report in a PDF format using Oracle Application Express 3.0. I'm not sure what this means and how to fix it. I'm running Oracle 10g on Windows Vista.

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1186
ORA-12570: TNS: packet reader failure

This is the first time I'm attempting to print to a PDF. I did follow configuration according to the Oracle documentation. I selected the Standard configuration that uses the built-in Apache FOP or another standard XSL-FO processing engine.

View 6 Replies View Related

RMAN Restore In Standard From Enterprise

Jul 30, 2012

i can do a rman restore from 9i Enterprise to 9i Standard?

View 2 Replies View Related

Downgrade From Enterprise Edition To Standard

Feb 10, 2011

I am checking out licenses. We all know that EE is much more expensive than SE. But many customers do have EE installed - unsure if they need all the features at all. After several years of production, a downgrade is considered 'risky' and we continue to pay the full EE.

How can we check and be sure that a downgrade to SE would not be any problem?

Some checks include:
* partitioning used in user schemas? --> no downgrade to EE
* bitmap indexes in user schemas? --> no downgrade to EE

How can we complete this list, or is there some script to make this easy?

View 7 Replies View Related

SQL & PL/SQL :: Return A List Of Non Standard Char

Oct 9, 2013

I have been tasked with producing a list of all non standard characters that are not one of 0-9, a-z, A-Z from a parts table. The list should capture any non-English characters i.e. áóú etc as well as !"£$%^&* etc. These are a small example of the data.

Part_Description
Probengefäss kompl.
Stützring zu Probenehmer
Zargendichtung m.2 Ventillöcher
Pneumatikdichtung m.2 Ventillöcher

Result from above example
ä.üö

View 8 Replies View Related

SQL & PL/SQL :: Use ANSI Standard JOIN Syntax?

Oct 9, 2013

Use ANSI standard JOIN syntax for example i have this code.

SELECT resv_num, unit_date
FROM p_resv_unit ru, p_pm_unit_night pun
WHERE pun.property_id = in_property_id
AND pun.pm_unit_num = cvUnitNum
AND pun.unit_date BETWEEN start_date AND end_date
AND pun.resv_unit_id = ru.resv_unit_id;

[code]....

and is it a good idea to change it, because both ways it works?

View 15 Replies View Related

Server Administration :: Upgrade From 10G Standard ED To EE ED

Feb 12, 2012

We are going to upgrade our production database from Oracle10g Standard ED to Oracle10G EE.

View 5 Replies View Related

Patch And Non-Standard Block Size

Apr 14, 2013

1) When might one choose non-standard block sizes?
2) Why do we need to apply patch? Just to fix bugs?

View 3 Replies View Related

SQL & PL/SQL :: Timestamp In ISO 8601 Compliant Standard

Mar 25, 2010

My scenario is like

i need to insert and retrieve the date fields in

ISO 8601 compliant standard ie..YYYY-MM-DDThh:mm:ss±hh:mm.

I have many locations to get reflected.

View 18 Replies View Related

Allocation Of Memory In Standard 10g Or 11g Install

Jan 17, 2013

I wanted to know what is the standard memory allocation for SGA & PGA in a normal installation of Oracle database 10g or 11g? Are the figures of 270M and 90M standard for a 10g or 11g installation using automatic memory management feature?

View 9 Replies View Related

Forms :: Restrict Standard Save Functionality?

Jun 20, 2011

how can we restrict standard save button functionality in custom form?

View 4 Replies View Related

Server Administration :: Non Standard Block Size

Aug 15, 2011

I am using oracle 10g with sga_max_size =4GB and db block size 16k. Now i am creating a tablespace with block size 32 kb , whats value i select for the parameter db_32k_cache_size.

Is there any standard way to calculate the value of this parameter.

View 4 Replies View Related

Performance Tuning :: AWR Report In 11g Standard Not Supported?

Jan 15, 2011

I was trying to generate AWR report, but the report which got generated consist most of the sections without data. Later i came to know that AWR report is not fully supported in 11g? Is that true?

View 6 Replies View Related

Forms :: Convert It Into Standard Date Format?

Jun 3, 2010

i have create one standard Calender from that i pickup month date and year separately like 2/6/1987 now i want to convert it into standard date format how to convert it and pass to another block....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved