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.
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]....
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.
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
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 )
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 .
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
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?
,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:
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.
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)
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]....
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
I installed oracle11g on my computer ,when im going to build demo its not working.its giving a error like this.
sys>conn u1/u1 error accesing PRODUCT_USER_PROFILE warning product user profile information not loaded you may need to run PUPBLD.SQL AS SYSTEM connected u1>@?/sqlplus/demo/demobld unable to open file "/oraeng/app/oracle/product/11.1.0/sqlplus/demo/demobld.sql"
We have developed a code which builds a trigger on a atble dynamically at run time usinf Dynamic SQL command Execute immediate.
create or replace PROCEDURE GENERATE_TRIGGER ( P_TNAME IN VARCHAR2
[Code].....
I am not usre if the error is due to insufficient privileges as we are able to build tabl;e in the system dynamically using the above mentioned command.
I am an absolute neophyte at this. I'm fairly good at SQL, but have not administrated a database before.
I am trying to take a backup from our production database and restore it to a fresh install of Oracle 10.2 on a Windows 2003 server using RMAN - but I am not getting very far. Software versions are all the same. I have access to the manuals, but it seems like every link I follow adds more questions than it answers.
I have gone through the wizard and set up oracle on the new box - that all seemed to go smoothly. In order for this to work, do I need to set up the database with the same DBID as the one in the backup files? Or is there a way to import the data from the backup and have it build the database from those files?
I'm not sure I'm even asking the right questions. Do any of y'all know of a tutorial that would walk me through this process step by step?
We have an requirement to create xml data for entire database (selected tables) which are in hierarchy.Procedure should read node_mapping table having parent and child tables relationship info and build XML Select statement.
Currently it is building SQL statement whenthere are one parent having multiple childrens i.e Dept having emp, emp_act, emp_rsch..but when child node are having childrens then it is not working - it has to repeatedly call this procedure (recursive) and build below given SQL statement.
1. To change procedure to build xml sql statement when there are multiple childrens to child nodes (hierarchy) 2. To format the output in xml data
We have an requirement to build (XML) Select statement dynamically based on the mapping table (which is in hierarchy format)
I have removed xml keyword, so query will not work. but need procedure which will recursively check mapping table (parent and child table) and build below given select statement.
I want to build a hierarchy and output it into a sub-report. Since the data in few hierarchy fields exceeds 4000 characters and therefore m getting the ORA-01489 error and the entire report was failing.
So i have used user aggregate function to print the out put. I have also used row_number() to print sequence in query output.
ID Causality -- ------ 3755101 )Blisters (Blister) Causality as per report1: Probable Causality as per report2: Possible Dechallenge : Not Applicable Rechallenge : Not Applicable 2 )Mental status changes (Mental status changes) [code].......
but the expected out put is
ID Causality -- ------ 3755101 )Blisters (Blister) Causality as per report1: Probable Causality as per report2: Possible Dechallenge : Not Applicable Rechallenge : Not Applicable [code]........
but it should be as
1) 2) 3) .. .. .. 10) 11) 12) for the values in the causality field
I have created a VIEW with a column containing user-defined aggregated values(stores the output in varchar2 data type). Some of the records exceed the maximum limit, for example varchar2(4000). I was suggested a PL/SQL function to check the length and build a string if necessary..
CREATE OR REPLACE FUNCTION SUBURL(col1 IN VARCHAR2, col2 IN varchar2) RETURN varchar2
[Code].....
Here, I got the following error,
Error(4,25): PLS-00103: Encountered the symbol "," when expecting one of the following: := . ( @ % ; not null range default character
I have built a Menu Framework using the following instructions.
[URL]........
I am facing 2 problems.
1. If the user is in a child application and clicks logout. He is taken to the login screen of the master application (Correct), but once he enters login details, he is returned to the child application (False). I think he should be returned to the Home page of the Master application.
2. If I set Session Timeouts in the Edit Security Attributes of the applications. Then if the user is timed out, he is sent to the login screen of the Master application (Correct). If he then logs in, he is sent the the Home page of the Master application (Correct).
Now if he clicks a link to a child application he is then sent back to the Master application login screen. He then has to login again. He is sent to the Master Home page and everything is then ok.
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
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?
I am trying to modify an existing form in oracle apps. I copied all the necessary objects and modified the form. I can compile the object in windows. When I am trying to compile the object in Linux server using SSH I am getting the following error.
FRM-91500: Unable to start/complete the build.I am using the following command for compile
