I need to update the TEMP_GP_SEQ table and on the COLUMN COL7 based on the grouping range mentioned above using the sequence created. But the sequence should not be incremented for each and every record, it should be incremented only for change of groups. For example
Actually the group by fetched two rows with a total record count of 4 (2 records in both the rows). SO the COL7 is updated for these four rows with M as a prefixed word and the number followed after M is from the sequence. Here the number is changing (sequence is incrementing) only when there is a change in grouping criteria and it remains the same throughout a group.
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select c.Event, c.code, count(1) as calls, total.total_count, count(1) / total.total_count * 100 as pct_of_total from table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
how to logically connect two tables. Here is an example of what I'm trying to do:
HOSTS TABLE CPU TABLE ----------- --------- ID HOST ID CPU 01 host1 01 proc01 02 host2 01 proc02 03 host3 02 proc01 02 proc02 02 proc03 02 proc04 03 proc01
Based on the above, I can see that 'host1' has 2 CPUs, 'host2' has 4 CPUs, and 'host3' has 1 CPU. What I'd like to do is create a query that would output:
HOST CPU ---------- host1 2 host2 4 host3 1
I'm looping through the hosts and passing the them into another query as bind variables. That's slow and cannot be the best way to do this. I'm aware that I need to using a GROUP BY HAVING COUNT, but that doesn't seem to be working. It tends to return the total count of all CPUs rather than CPUs per host.
My requirement is Data from a TableA has to be provided as an overall view
TABLEA ID ENTITY REQ_FLG PAR_FLG EXT_FLG CONV_1 ACCNT Y Y Y CONV_1 PROD Y Y N CONV_1 ADDR Y N N CONV_2 DID Y N N CONV_2 ORDER Y N N
Required to show the data in report as
ID Expand View_Report Populate ENTITY QRY_STATUS CONV_1 Expand Report Populate ACCNT Y Y Y PROD Y Y Y ADDR Y N N CONV_2 Expand Report Populate DID Y N N ORDER Y N N
Where "Expand", "Report", "Populate" are provided as Hard coded values in query.
Sample Query. SELECT ID ,'Expand' AS EXPAND ,'Report' AS VIEW_REPORT , 'Populate / Reset' AS POP , DECODE(MN_TBL.ENTITY,NULL,NULL,ENTITY) AS ENTITY , REQ_FLG || ' ' || PAR_FLG || ' ' || EXT_FLG AS QRY_STATUS FROM TABLEA GROUP BY GROUPING SETS ((ID), (ENTITY, REQ_FLG , PAR_FLG , EXT_FLG )) ORDER BY CONVERSION_ID, ENTITY
Above query works fine, where single ID is present
ID Expand View_Report Populate ENTITY QRY_STATUS CONV_1 Expand Report Populate ACCNT Y Y Y PROD Y Y Y ADDR Y N N
But when more than one ID is present the entire thing collapses
My weekly reports should start from Thursday and it ends on Wednesday . I need to aggregate data from Thursday to Wednesday. At the end of every month i generate report for the last 4 weeks.
I couldn't find a way to group by correctly. When I make group by trunc(fragment_date,'day')+2 and check the data it aggregates data from Monday to Sunday.
SELECT COUNT(c.country_id) FROM countries c, employees e, locations l, departments d
[Code]....
I really dont know when and where to use the group by statement from the query above when I added C.country name before the word from I'm always getting this error "ORA-00979: not a GROUP BY expression"
This time, I am going to provide the DML statements.
I have a simple table with 3 fields in it.I want to group by ACCT_NUMBER and sum of BALANCE field. But I want to get the description of the first row in a group.
the statements below. Here there are two groups of records 2001 and 2002. My sql(which I am working on) should return the following :
2001 EMPL TRAINING-MIS 20 2002 OTHER PROF SERV-HR 40
The following query will group by ACCT_NUMBER and sum of the BALANCE field. But how can I get the DESCRIPTION?
SELECT ACCT_NUMBER, SUM(BALANCE) FROM TEST GROUP BY ACCT_NUMBER CREATE TABLE "TEST" ("ACCT_NUMBER" VARCHAR2(20 BYTE), "DESCRIPTION" VARCHAR2(20 BYTE),
Here is the scenario with examples. Big table 333 to 500 million rows in the table. Statistics are gathered. Histograms are there. Index is not being used though. Why?
CREATE TABLE "XXFOCUS"."some_huge_data_table" ( "ORG_ID" NUMBER NOT NULL ENABLE, "PARTNERID" VARCHAR2(30) NOT NULL ENABLE, "EDI_END_DATE" DATE NOT NULL ENABLE, "CUSTOMER_ITEM_NUMBER" VARCHAR2(50) NOT NULL ENABLE, "STORE_NUMBER" VARCHAR2(10) NOT NULL ENABLE, "EDI_START_DATE" DATE,
[Code]...
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT num_rows FROM user_tables s WHERE s.table_name = 'some_huge_data_table';
NUM_ROWS ---------- 333338434
SQL> SELECT MAX(edi_end_date) 2 FROM some_huge_data_table p 3 WHERE p.org_id = some_number 4 AND p.partnerid = 'some_string';
MAX(EDI_E --------- 13-MAY-12
Elapsed: 00:00:00.00
SQL> explain plan for
2 SELECT MAX(edi_end_date) 3 FROM some_huge_data_table p 4 WHERE p.org_id = some_number 5 AND p.partnerid = 'some_string';
Why wouldn't it use the index in the group by? If I write a loop to query for different partnerid (there are only three), the whole things takes less than a second.
btw, I gave the index hint too. Didn't work. Version mentioned in the example.
select T2.name,sum(T1.area) from (select * from country) T1,T2i wrote very simple just to show you the problem, bu i think here we should add "group by". but is there any way to avoid group by?because i don't want to group any thing!
i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),
or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),
when i am running the below plsql block i am getting the error like not a group by expressiong.
DECLARE CURSOR Cur_st IS SELECT DISTINCT CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE, CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE, CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO, CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE, TO_DATE (A.SALES_DATE, 'YYMMDD'), CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)), [code]....
I have the following table and data , I have six employees and carton named A,B,C,D,E and F.
Drop table a ; Create table a (id number(9), emp_id number(9), cartoon varchar2(20), no_cartton number(9)); Insert into a values(1,1,’A’,10); Insert into a values(2,1,’B’,20); Insert into a values(3,1,’D’,25); Insert into a values(4,1,’E’,15); [code].......
I have also Third Query: the first and second query already solved in this link . Sum based on group
it is multiple value of carton A of emp_id 1 with 2 + multiple value of B of emp_id 1 and 2 and so on
More detalies : Multiple value of A for Empid 1 and 2 then add it to multiple value of multiple value of b for empid 1 and 2 then add it to multiple value of c of empid 1 and 2
After finished all carton for 1 and 2 then go to 1 and 3 after finished go to 1 and 4 then 2 and 3 then 2 and 4 and so on
Output will be like this Empid Total
1 with 2 2050 1 with 3 200 1 with 4 500 1 with 5 2250 1 with 6 700 2 with 3 0 2 with 4 700 2 with 5 4300 2 with 6 1700 3 with 4 0 3 with 5 400 3 with 6 0 4 with 5 400 4 with 6 200 5 with 6 1900
What is the optimal redo log size for the database and how many log files required if desired to enable archive log mode.what can be the value for fast_start_mttr_target..?i think if it this parameter set we can have redo log advisor for optimal redolog size.we have 2 redolog groups with 2 members each size of 1 GB. Will it degrade db performance..?
Database version 11.1.0.7 Oracle apps R12 OS : Linux Redhat 5.5
I have 5 MViews that I want to refresh in two occassions, every sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".
You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?
select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags" from p_in where order_date between '1-apr-11 and '31-mar-12' group by to_char(order_date,'mon-yyyy')
the above query returns me a resultset which gives me number of bags sold in every month, but it does not sort the month in their chronological order. i want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...QUOTE
I want to re-size redo log group on my production database .i have 10 redo log groups of 50mb each having 2 members.i want 4 redo groups to be of 250 mb each having 2 members and then i will drop that old 10 redo log group(50 mb ) , so that i will have only 4 redo log group of 250MB each having 2 members.But i have physical standby and logical standby configured on production database .
find attached file for redo log configuration of production database(CBSPROD),Logical standby database(CBSMIS), Physical standby database(CBSDR).
