SQL & PL/SQL :: GROUP BY

Oct 10, 2011

CREATE TABLE TEMP_GP_SEQ
(
COL1 NUMBER,
COL2 NUMBER,
COL3 NUMBER,
COL4 NUMBER,
COL5 NUMBER,
COL6 NUMBER,
COL7 CHAR (25)
);

[Code]...

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

SELECT *
FROM TEMP_GP_SEQ;

COL1 COL2 COL3 COL4 COL5 COL6 COL7
110100
110101
12101001

[Code]...

After update it should look like

SELECT *
FROM TEMP_GP_SEQ;

COL1 COL2 COL3 COL4 COL5 COL6 COL7
110100
110101
12101001M2

[Code]...

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.

View 19 Replies


ADVERTISEMENT

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

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.

View 4 Replies View Related

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

Jun 23, 2011

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 ?

View 16 Replies View Related

SQL & PL/SQL :: Group Through Expression Inside Group By?

May 17, 2011

Refer to the txt file to create table and insert data.

I executed the following query-

SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail

and got the following result-

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1

Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1

View 3 Replies View Related

To Add One Disk Group Space To Another Disk Group

May 9, 2011

Is there any way to add one disk group space to another disk group. Because One of my disk group is full i want to add space in to that group.

View 1 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

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]

View 4 Replies View Related

Using GROUP BY To Get Counts

Jan 22, 2008

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.

View 2 Replies View Related

Group Policy In VPD

Jun 19, 2013

Give me some examples of creating grouped in VPD .

Group policy example for
1) Grouped policy on same table for different columns
2) Grouped policy on two different table for different columns

View 1 Replies View Related

SQL & PL/SQL :: Index And Group By

Sep 5, 2012

I have a query that extracts the row with a max(record_date) within a group.

select ssn, fund, type_indicator, annuitant, cur_year, record_date, guarantee
from LC_MORTALITY
where (ssn, fund, type_indicator, annuitant, cur_year, record_date) in
(select ssn, fund, type_indicator, annuitant, cur_year, max(record_date)
from LC_MORTALITY
group by ssn, fund, type_indicator, annuitant, cur_year);

the table has index that matches the group by clause exactly.

create index IDX_LC_MORTALITY_sftayd on LC_MORTALITY
(SSN,
FUND,
TYPE_INDICATOR,
ANNUITANT,
cur_year,
record_date

However, the plan ignores the index

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1963K| 241M| | 1375K (4)| 05:21:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 1963K| 241M| 9701M| 1375K (4)| 05:21:04 |
| 2 | VIEW | VW_NSO_1 | 145M| 8038M| | 241K (12)| 00:56:22 |
| 3 | HASH GROUP BY | | 145M| 8038M| | 241K (12)| 00:56:22 |
| 4 | TABLE ACCESS FULL| LC_MORTALITY | 145M| 8038M| | 219K (3)| 00:51:13 |
| 5 | TABLE ACCESS FULL | LC_MORTALITY | 145M| 9840M| | 222K (5)| 00:51:49 |
---------------------------------------------------------------------------------------------
[code]...

It uses the index but still shows "table access full" in one place. Why is there still a full access?Can I do anything to optimize further?

View 9 Replies View Related

SQL & PL/SQL :: How To Group By Data

Sep 8, 2011

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

View 5 Replies View Related

SQL & PL/SQL :: Group By Thursday

Jun 26, 2013

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.

View 7 Replies View Related

SQL & PL/SQL :: Group By Statement

Oct 27, 2011

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"

View 12 Replies View Related

SQL & PL/SQL :: Group By Clause

Mar 31, 2011

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),

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Selecting 1 Row From A Group?

Jul 3, 2011

I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.

CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)

[code]....

View 17 Replies View Related

SQL & PL/SQL :: Group By For Getting Count

Mar 7, 2013

I need suing group by for getting count.I have a table with columns below

SYS_AUDIT_IDSYS_AUDIT_PROG_IDPROG_FINDING_ID_COUNT

178921652
178921641
178921631
179321521
179321511
179321501
179321491
179321461

I want to count number of SYS_AUDIT_PROG_ID for each audit and count of PROG_FINDING_ID_COUNT

I want to get
1789 3 4

I tried this query but this is not working

[code]select sys_audit_id ,count (sys_audit_prog_id), count(prog_finding_id_count) from

my_table sub
group by sys_audit_id [/code]

View 2 Replies View Related

SQL & PL/SQL :: Other Columns When Using Group By

Feb 18, 2011

I have a table OS_CURRENTSTEP , and OS_WFENTRY

CREATE TABLE OS_CURRENTSTEP
(
ID NUMBER,
ENTRY_ID NUMBER NOT NULL,
STEP_ID INTEGER NOT NULL,
ACTION_ID INTEGER,
OWNER VARCHAR2(20 BYTE),
START_DATE DATE,

[Code]...

I need count of step_id from os_currentstep wh

here is the query

select count(step_id), step_id from os_currentstep where owner='Marty' group by step_id

this gives me the count I also need the name associated with this step_id from table OS_WFENTRY

, I cannot query name from step_id ,

View 5 Replies View Related

SQL & PL/SQL :: Group Functions

Jul 2, 2010

count the no: of emp working under each manager? and instead of manager number display the manager name

View 5 Replies View Related

SQL & PL/SQL :: Group By Query

Nov 5, 2011

I am new to PL/SQL, could you pls let me know how to solve this requirement using PL SQL

C-A | C-B
A123 | -1
B334 | 4
B567 | 2
B333 | -1
T777 | 2
Y774 | 3
T879 | 4
T654 | 3
T474 | 0
Y432 | -1

I need the output like this

C-1|C-2|C-3|C-4
3 |7 |3/10|7/10

Column-1 (Count of rows where Column-B = -1)
Column-2 (Count of rows where Column-B in(0,1,2,3,4)
Column-3 (Column-1/Total rows)
Column-4 (Column-2/Total rows)

View 5 Replies View Related

PL/SQL :: Index Not Being Used In Group By

Sep 14, 2012

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';

Explained.

SQL> /

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 2104157595                                                                                                                                                                           
--------------------------------------------------------------------------------------------       
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |       
--------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT             |             |     1 |    22 |     4   (0)| 00:00:01 |       
|   1 |  SORT AGGREGATE              |             |     1 |    22 |            |          |       
|   2 |   FIRST ROW                  |             |     1 |    22 |     4   (0)| 00:00:01 |       
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| some_huge_data_table_PK |     1 |    22 |     4   (0)| 00:00:01 |       
--------------------------------------------------------------------------------------------       

SQL> explain plan for

  2  SELECT MAX(edi_end_date),
  3         org_id,
  4         partnerid
  5    FROM some_huge_data_table
  6   GROUP BY org_id,
  7            partnerid;

Explained.

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 3950336305                                                                                                                                                                            
-------------------------------------------------------------------------------                    
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                    
-------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT   |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   1 |  HASH GROUP BY     |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   2 |   TABLE ACCESS FULL| some_huge_data_table |   333M|  6993M|  1592K  (1)| 05:18:33 |                    
-------------------------------------------------------------------------------      

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.

View 18 Replies View Related

PL/SQL :: Partition By Group?

Sep 14, 2012

below is the create and insert script.

create table sal_group (g_id number(10),sal_rand number(20));
/
INSERT INTO sal_group values(1,5000)
/
INSERT INTO sal_group values(1,2000)
/
INSERT INTO sal_group values(1,4000)

[code]...

/I have tried

SELECT g_id,sal_rand,
case when row_number() over (order by g_id) = count(*) over () then 'yes' else null end as row_check
  FROM sal_group output:

G_ID    SAL_RAND    ROW_CHECK

1       4000   
1       2000   

[code]....

View 3 Replies View Related

PL/SQL :: Way To Avoid (group By)

May 14, 2013

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!

oracle11g

View 4 Replies View Related

PL/SQL :: Returning Same Value Set In A Group

Jan 24, 2013

I have the below requirement,

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),

View 2 Replies View Related

PL/SQL :: Not A Group By Expression

Apr 1, 2013

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]....

View 19 Replies View Related

PL/SQL :: Sum Based On Group 2

Nov 26, 2012

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

View 2 Replies View Related

PL/SQL :: How To Group By Over A Range

Jun 12, 2012

I have a table like this:

x     y     AMNT
---------------------------------------
1     120     12
1     120     93
1     125     31
1     260     15
2     56     16
2     115     49
3     45     71
4     19     11
4     16     48
5     94     52
5     98     47

I want to group records on x , y columns and aggregate on amnt column, in which difference between values of y column be less than 10.

The result is like this:

x     y     sum(AMNT)
------------------------------------------------
1     ?     136
1     260     15
2     56     16
2     115     49
3     45     71
4     ?     59
5     ?     99

What query can I use?

View 6 Replies View Related

Get Redo-log Group Sizing?

Aug 22, 2012

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

View 1 Replies View Related

Query For Group By Date?

Apr 30, 2011

We have one oracle table, which has one column "Creation_Date" of the datatype date.

This column has data like
"14-Aug-2010 01:30:30"
"14-Aug-2010 03:30:30"
"15-Aug-2010 01:30:30"
"16-Aug-2010 01:30:30"
"16-Aug-2010 03:30:30"

We want to write SQL query which will group rows by date wise without considering time..I mean for above rows the date wise output should be

Count Date
------- -------
2 14-Aug-2010
1 15-Aug-2010
2 16-Aug-2010

View 2 Replies View Related

MView In More Than One Refresh Group?

Sep 11, 2013

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?

View 1 Replies View Related

Group By With Order By For Dates?

Mar 9, 2013

ii have written a query like thi:

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

View 1 Replies View Related

Redo Log Group Resize

Mar 20, 2012

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).

View 1 Replies View Related







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