SQL & PL/SQL :: Conditional Average Calculation Of Column?

Jul 22, 2012

I want to write a sql qeuery to get result similar to expected average column as shown in sheet.

Record can be uniquely identified by columns ID, PRODUCT and OFFICE. I want to calculate average for each date considering the same record does not exists earlier i.e. I want to consider the latest vote while calculating average. E.g.

Average till date 1/1/2011 is Avg (2, 4, 3) = 3 (as no repeating vote value exists)
Average till date 2/1/2011 is Avg (4, 3, 5, 3) = 3.75 (excluding vote value 2 as 122_UK_LONDON was provided his vote earlier, so considered latest vote value i.e. 5)
Average till date 3/1/2011 is Avg (3, 5, 3, 6, 5, 8 ) = 5 (excluding vote value 2 for 122_UK_LONDON and vote value 4 for 967_Europe_London)

View 15 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 :: Display Conditional Column Values

Apr 23, 2013

I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values

For example:

So when dept.deptno=10, I want to display these 2 columns values
1. dept.deptno
2. dept.dname

otherwise, display these 2 columns values
1. dept.loc
2. null

Can this be done with one case, decode or "other" type of structure going thru the table one time??

SELECT emp.empno,
emp.ename,
CASE
WHEN dept.deptno = 10 THEN
to_char(dept.deptno)
[code].......

View 6 Replies View Related

PL/SQL :: Conditional Transpose Of Column Data Into Rows

Jul 26, 2013

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production versionI have following table - 
drop table t2;

create table t2(BATot,Ly_BATot,LLy_BATot,BScTot,Ly_BScTot,LLy_BScTot,BAMSTot,Ly_BAMSTot,LLy_BAMSTot) as select
5000,2000,12600,20000,45600,35000,45000,56000,65000 from dual ;

select null class, batot,ly_batot,lly_batot,bsctot,ly_bsctot,lly_bsctot,bamstot,ly_bamstot,lly_bamstot from t2; Simple DML I am using -
SELECT * FROM T2;

C      BATOT   LY_BATOT  LLY_BATOT     BSCTOT  LY_BSCTOT LLY_BSCTOT    BAMSTOT LY_BAMSTOT LLY_BAMSTO
- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        5000       2000      12600      20000      45600      35000      45000      56000       65000

[Code]....

View 7 Replies View Related

PL/SQL :: Calculation On Sql Column Layer

Aug 31, 2012

V: 10.2.0.3

I want to calculate on column-layer of a sql-query:

select a "column1", "column1" + 1

from ...but it does not work...

View 9 Replies View Related

SQL & PL/SQL :: Average Over Time

Sep 27, 2011

I have a table with products (xPRODUCT), dates (xDATE) and parameters (xPARAMETER). Now I want to calculate the rolling 12 month average of the parameters over all products for all dates. I tried this:

select xDATE,
avg(xPARAMETER) over ( order by xDATE
range between numtodsinterval(365,'day')
preceding and current row )

[Code]....

... but this does not work. How can I do this?

View 5 Replies View Related

SQL & PL/SQL :: AVERAGE By Hour

May 9, 2010

I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.

SERVER_NAME DATE_TIME PRO_VITUAL_BYTES

MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00

[Code]....

View 4 Replies View Related

Forms :: How To Calculate Average In 6i

Apr 5, 2011

How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .

View 2 Replies View Related

SQL & PL/SQL :: Average Of Time In HH:MM:SS Format

Jun 17, 2010

how to calculate the average of the time in th e HH:MM:SS format stored database table.. column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as timestamp(6).

MY COL
------
1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00

[Code]...

View 29 Replies View Related

PL/SQL :: Get Sum Average And Projected Totals In Same SQL

Nov 23, 2012

Version : 11g

I have a table with the following format and data

Serial no    exp_Date  exp_type   exp_amount
1              01-nov-2012   Rent       10000
2              02-nov-2012   gas          250
3              02-nov-2012   insurance   9500
.
.
.

I want to create a sql output for a yearly view in the format

   exp_type        JAN            FEB      MAR        APR ..... NOV  DEC          TOTAL     AVERAGE    PROJECTED
    Rent             10000        10000   10000     10000   10000  10000      120000     10000        120000
    gas                250             250     250        250      250      250          3000         250           3000
.
.

Now a couple of things in this

1. the average gives the average for the year, so lets say its start of 2013 and we are in feb, there will not be any values for the remaining months, so it should do the average for that exp_type for Jan and Feb based on the exp_amount entered against that type and show what is the expected average. Similary, projected will that average amount and mulitply it by 12 to show the exp amount expected based on current expenses

I was able to come up with the following sql to get the sum based on months, was not sure about average, total and projected

SELECT       exp_type
,        SUM (CASE WHEN to_char(exp_date,'Mon') =  'Jan' THEN exp_amt END)     AS Jan
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Feb' THEN exp_amt END)     AS feb
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Mar' THEN exp_amt END)     AS Mar
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Apr' THEN exp_amt END)     AS Apr
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'May' THEN exp_amt END)     AS May


[Code]....

getting the correct avg, total and projected fields also in the same sql?

View 8 Replies View Related

Load Average In AWR Report?

Jul 26, 2013

,I've some database in 11.2 RAC on AIX.I was analyzing the root causes of eviction.Looking AWR Report before the reboot I see: 

DB1  Host CPU (CPUs:    6 Cores:    3 Sockets: )~~~~~~~~         Load Average               Begin       End     %User   %System      %WIO     %Idle           --------- --------- --------- --------- --------- ---------                4.18     12.33     60.9      12.6       1.6      26.5  Instance CPU~~~~~~~~~~~~              % of total CPU for Instance:      27.4              % of busy  CPU for Instance:      37.3 %DB time waiting for CPU - Resource Mgr:      10.6  DB2   Host CPU (CPUs:    6 Cores:    3 Sockets: )~~~~~~~~         Load Average               Begin       End     %User   %System      %WIO     %Idle           --------- --------- --------- --------- --------- ---------                3.77    13.93     60.7      12.5       1.6      26.7  Instance CPU~~~~~~~~~~~~              % of total CPU for Instance:       6.9              % of busy  CPU for Instance:       9.5  %DB time waiting for CPU - Resource Mgr:       0.0 

Do you think these value ar high? This is vmstats at the time of reboot:

  DATARUNBCKAVMFREPREPPIPPOPFRPSRPCYFINFSYFCSCUSCSYCIDCWA07/21/2013  00:08:173107.400.345579.92308100003.292187.01019.56084160007/21/2013  00:08:171717.390.187589.884017600003.681169.99421.48281190007/21/2013  00:08:172717.402.121577.816011500003.150157.21018.50384160007/21/2013 

[code]...

View 3 Replies View Related

PL/SQL :: Sql Query To Build Average Value

Jul 18, 2013

I'm trying to build a query capture the below information. Table looks something like below . 

RoomSubjectStudentsA1Science10A1Maths20B1Science15B2English25C1Deutsch20A1French15C1English10 

How can i write a query so that i takes the average of students in each room and display the output? I need to write a query so that it also picks up any new rooms and students added.  Average is sum of total students in room by number of times each room is specified.  

eg: A1 is listed 3 times a the result would be 10+20+15 divided by 3.    

A1    B1    B2    C115    15    20    15 

View 7 Replies View Related

SQL & PL/SQL :: Cursor Calculate Number / Average / If

Dec 15, 2011

I have to procedure that computes number of project, and average working hours of employees where employee id is passed as a parameter to the procedure. If the average working hours is less than 10 then employee's salary remain the same, otherwise check if number of project is less than 4 then 5% of salary, else 10% of salary is added to the salary.

my tables are:

CREATE TABLE employee(
empid number(5),
empname varchar(20),
address varchar(20),
no_of_dependents number(5),
deptno number(5),
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(empid),
CONSTRAINT EMPLOYEE_FKEY FOREIGN KEY(deptno) REFERENCES department(deptno));
[code]...

the problem is i have to use only 2 cursors - 1 for select and 1 for update.

View 27 Replies View Related

SQL & PL/SQL :: Analytical Query Moving Average

Aug 10, 2013

I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.

create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....

View 17 Replies View Related

SQL & PL/SQL :: Convert Query To A Rolling Average?

May 2, 2013

I have the following query that creates sums by week.

SELECT cidterr.rnam,
cidterr.rnum,
cidterr.tnam,
cidterr.tnum,

[code]...

I want to convert this query to just return a single line for -cidterr.rnam, cidterr.rnum, cidterr.tnam, cidterr.tnum

With an average sum by week. Similar to how if I did a sum by week from the original query and placed the results into an excel pivot and said show total as average.

View 3 Replies View Related

PL/SQL :: Average In Tree With Partial Results

Feb 27, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE     11.2.0.1.0     Production"
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

I have a problem related with hierarchical queries, I have this tree.
-    a
    /
   b   c
/ |
d  e  fI need to calculate an average per node but using always the child's results for example: Only leafs will have values:

d = 10
e = 20
f = 30
c = 40So the expected result is:
d = 10
e = 20
f = 30
c = 40
b = (10(d) + 20(e) + 30(f)) / 3 (number of child nodes) = 20
a = (20(b) + 40(c)) / 2 (number of child nodes) = 30.

I have tried with recursive queries, hierarchical queries, I guess it's possible with model too but I can't produce the exact results that I need. Maybe in fact there is a very simple solution but I cannot figure it.

Here is an auxiliary WITH that you can use to start your tests:
WITH tree AS
(
     SELECT 'd' child, 'b' parent FROM dual UNION ALL
     SELECT 'e' child, 'b' parent FROM dual UNION ALL
     SELECT 'f' child, 'b' parent FROM dual UNION ALL
     SELECT 'b' child, 'a' parent FROM dual UNION ALL
     SELECT 'c' child, 'a' parent FROM dual UNION ALL
     SELECT 'a' child, null parent FROM dual
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Average For Month (no Weekend Data In Table)

Jun 10, 2011

I've got values for each workday of month in table. Chief said we don't load values of weekend because of space and time economy. Weekend value is copy of last workday. How do I count average for month in this case? Technical task in Excel worksheet consist of each day of month, so average value is 14 689 262,86. I should get the same result in my query. My example below shows values, 0 is workday (present in table), 1 is weekend (absent in table)..

select 12230427, 0 from dual
union all
select 11960157, 0 from dual
union all
select 12965902, 0 from dual
union all
select 13939736, 0 from dual

[Code]...

View 31 Replies View Related

RAC & Failsafe :: Global Cache Average CR Get Time?

Mar 4, 2012

from last two weeks we are receiving rhe below database alert on our second NODE (RAC ENV).

Global cache Average CR get time " is at .61789
Threshold is set on >.5 (M.Second).

me to figure this out because i really dont know the nature of this alert. what value i can set for the warning threshold.

View 1 Replies View Related

SQL & PL/SQL :: How To Do Conditional Query

Mar 22, 2010

how to do a conditional range in a query. For example, I have two tables:

table A:
a1 char(1)
a2 char(1)

table B:
b1 char(1)
b2 int

In my query I want to range on a different value in the b2 field depending on what is in the fields in the A table. For example, if the a1 field is Y, I want to look up the record in B with a b2=20. If a2 is Y, I want to look up b2=5. Otherwise I want to look up b2=1. I attempted the following:

select b1 from b, a where b2=(if a1='Y' then 20; elsif a2='Y' then 5; else 1)

but that doesn't work.

View 8 Replies View Related

SQL & PL/SQL :: Conditional Selection Of A Field

Feb 27, 2013

I'd like to select one of two fields from a table, based on which of them is greater for that row.

e.g. For every row in JOINING_TBL, I'd like to select either JOINING_DATE or REJOINING_DATE, whichever is greater. I do dream about being able to use a Java-like ternary operator in here, something like this:-

SELECT (JOINING_DATE>REJOINING_DATE?JOINING_DATE:REJOINING_DATE) FROM JOINING_TBL

However, I understand(through other fora) that this can't be used in here.

View 3 Replies View Related

SQL & PL/SQL :: Conditional Sequence Usage?

Jan 31, 2013

I'd like to have a conditional that only gets the next value from a sequence is the current (or supplied) value is null.

Here are some trivial examples:

CREATE SEQUENCE ts
MINVALUE 1
START WITH 1
INCREMENT BY 1;
SELECT ts.NEXTVAL FROM DUAL;
SELECT COALESCE(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT NVL(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT CASE
WHEN ts.CURRVAL IS NOT NULL THEN ts.CURRVAL
ELSE ts.NEXTVAL
END
FROM DUAL;

Ideally multiple executions of any of these (or a better one of your design) should return the same value from the sequence, but mine do not.

View 6 Replies View Related

SQL & PL/SQL :: Oracle 9i - Conditional Statement

May 9, 2011

The conditional statements should not only be executed in sequence, but also if any of them are true they should not be overridden by any subsequent conditional statements being true.

When actual effort Accepted or Rejected for AST proposals and calculate a flag for "enhance to AST guideline" = Y/N as follows for each employee and display at the employee level

1)If AST eligibility = N AND proposed AST % >0, then "N"
2)Else If AST eligibility = N AND proposed AST % = 0 then "n/a"
3)Else If AST eligibility = Y AND Act Rank = 3 AND proposed AST = 0 then "Y"
4)Else If AST eligibility = Y AND Act Rank = 3 AND proposed AST >0 then "N"
5)Else If AST eligibility = Y AND Act Rank = 2 AND proposed AST = 0 then "Y"
6)Else If AST eligibility = Y AND AST % is greater than or equal to the AST guideline minimum AND less than or equal to the AST guideline maximum, then "Y"
7)Else If AST eligibility = Y AND AST % is less than the minimum guideline OR greater than the maximum guideline, then "N"

I tried the following code but I am not getting the expected result .

if (upper(P_stat)='ACCEPTED' or upper(P_stat) like 'REJECTED%') then
else if NVL(P_elgi,'N') <> 'Y' AND P_prop > '0' then
P_flag := 'N';
else if(NVL(P_elgi,'N') <> 'Y' AND P_prop = '0') then
[code]........

View 5 Replies View Related

SQL & PL/SQL :: Conditional Group By Of Rows

May 8, 2013

Task I would like to do in plain SQL rather than PL/SQL.

Given this table:

create table TEMP_TEST
(
DT_START DATE not null,
DT_END DATE not null,
FIELD VARCHAR2(2)
)

and these rows:

insert into temp_test (DT_START, DT_END, FIELD)
values (to_date('01-01-2005', 'dd-mm-yyyy'), to_date('31-08-2007', 'dd-mm-yyyy'), 'ML');
insert into temp_test (DT_START, DT_END, FIELD)
values (to_date('01-09-2007', 'dd-mm-yyyy'), to_date('31-12-2007', 'dd-mm-yyyy'), 'MT');

[Code]....

what i want to do is collapse the rows that have same field value, ONLY if the end date of the first one equals start date -1 of the next one.

that being said the result should be

01/01/2005 - 31/08/2007 - ML 01/09/2007 - 31/12/2007 - MT 01/01/2008 - 29/02/2012 - ML 15/03/2012 - 31/12/9999 - ML

I was doing this:

SELECT MIN(dt_start), MAX(dt_end), field
FROM(
SELECT dt_start,
dt_end,
field,

[Code]....

but it still misses the thing about adjacent time periods

View 6 Replies View Related

PL/SQL :: Conditional Delete Procedure?

Jul 25, 2013

I am trying to create procedure to delete data from audit table but don't know where to start with..

1. Procedure will run on first Saturday of the month (not sure is it possible via pl/sql or have to create separate job)
2. Delete data older than 2 months from Mon - Sat and date shouldn't be 1st of the month. (i.e. leave Sunday & 1st of the month data older than 2 months) e.g.{code}

Procedure delete_log
IS
BEGIN
delete from audit_logs
where created >= trunc(sysdate - 60) and created < trunc(sysdate)
and created != (Sunday)
and created != (First of the month);

View 10 Replies View Related

Server Administration :: Sun Solaris - Load Average Too High At Specific Days

Oct 16, 2010

My problem is only on Some Saturday's my Oracle server's Load average goes high (more than 300 ).

-- oracle version 11.2.0.1.0
-- runs on Sun solaris 10
-- Sun fire V 440
-- Sun storEdge 3315 connected to the server.

Same setup is working find with higher volumes without any problem but only on saturday's, that too not on all saturdays, some specific saturdays the load average goes high.

At the time nothing will be processed from the application side and the cpu utilisation goes high upto 95 %.

I am sending necessary information as follows.

vmstat 6 10
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s3 s4 in sy cs us sy id
3 0 0 28660024 6513408 285 212 2044 2 2 0 0 0 17 0 42 830 3469 1380 22 5 74
125 0 0 29027480 6156256 2 6 34 0 0 0 0 0 5 0 10 791 53770 30278 83 17 0
125 0 0 29027680 6157496 29 140 21 1 1 0 0 0 5 0 9 786 52756 30309 83 17 0
116 0 0 29031600 6159896 24 125 0 0 0 0 0 7 3 0 5 819 54081 31069 83 17 0

[code]....

View 1 Replies View Related

Meaning Of Lines CPU Cores And 99th Percentile In Average Active Session

Apr 18, 2013

I´m monitoring a 11g database with OEM. I have a couple of questions regarding the Average Active Sessions chart:

- What does the line 'CPU core' means? The DB is running in a virtual server with 8 CPUs. However, the line 'CPU core' is in 1. Does it mean that Oracle is just using 1 CPU?
- What represents the '99th percentile' line? The chart shows several sessions above that line, something is not working well, isn't it?

View 4 Replies View Related

SQL & PL/SQL :: Conditional Insert With Multiple Subqueries

Oct 9, 2012

We have multiple environments and our dev and UAT ones are now different from staging and live (I know, but I am not in a position to get this fixed). I have a set of updates that need to go through to live and in some cases they reference rows that do not exist in the UAT environment, and yet they have to (rigid, dumb process) go through that environment.

Basically, the insert I need to do takes info from two tables and does an insert into a third. That target table has a not null constraint on the affected fields, so the insert fails, quite rightly.

There's lots of info available on how to do conditional inserts with single sub-queries, using DUAL and EXISTS (or rather NOT EXISTS, but that's easy to swap), but those don't seem to easily translate for this one.

The sql that works when everything exists is:

insert into wmcontent.wm_manda_corpserv_companies
(wm_manda_company_code, wm_corp_company_code)
values
( (select wm_company_code from wmcontent.wm_m_and_a_company where wm_company = 'SW'), (select min(oid) from wmcontent.wx_category where content_type = 2 and name = 'SW') );

In desperation I even tried using "log errors reject limit unlimited" but, no doubt due to my misunderstanding of how that works, I ended up getting the error "ORA-06550: line 38, column 1: PL/SQL: ORA-00972: identifier is too long" as a result.

View 8 Replies View Related

SQL & PL/SQL :: Conditional Predicates Is Not Working With MERGE

Aug 6, 2012

I realized that in statement level trigger conditional predicates(INSERTING, UPDATING & DELETING ) are not working.

For example please run below sql:

create table merge_test(col1 number, col2 number);
create or replace trigger merge_test_tr_stat
after INSERT OR UPDATE OR DELETE on merge_test
begin

if inserting or updating or deleting then
dbms_output.put_line('conditional predicates is working ');
else
dbms_output.put_line('conditional predicates is not working ');
end if;
end;

set serveroutput on;
insert into merge_test values (1,1);

Output
------
1 rows inserted.
conditional predicates is working
conditional predicates is working

set serveroutput on;
merge into merge_test mt using ( select 1 col1 from dual ) tab
on ( mt.col1 = tab.col1 )
when matched then
update set col2 = 2
when not matched then
insert values (1,1);

Output
------
1 rows merged.
conditional predicates is not working
conditional predicates is not working

How to use those conditional predicates in statement level trigger.

View 3 Replies View Related

Forms :: Get Conditional Window Appear In Form

Jun 2, 2011

I want to achieve functionality like below:

Let's say there are two almost identical window and some specific functions associated: W1 and W2.

Depending upon the login profile into the application only one window and associated functions will be appeared. This time the other window will be disabled.

View 3 Replies View Related

Application Express :: Conditional List Of Values

Nov 15, 2012

Is there any way I could set the condition on the list of values.

For example if Value of the item x is null then use select ...

and when value of the item x is not null use this select ....

View 4 Replies View Related







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