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

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

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

SQL & PL/SQL :: ORA-00979 / Not A GROUP BY Expression

Jan 3, 2012

I wrote a query for minimum average salary of Job_id using inline view but it is not working..

The query is as follow

SELECT job_id, avg(salary)
FROM employees , (SELECT MIN(AVG(salary)) min_avg_sal
FROM employees
GROUP BY job_id) b
GROUP BY job_id
having avg(salary)=b.min_avg_sal;

It returns error as

################################
having avg(salary)=b.min_avg_sal
*
ERROR at line 6:
ORA-00979: not a GROUP BY expression
#########################

View 2 Replies View Related

SQL & PL/SQL :: Expression Column With A Group By Clause?

Mar 28, 2011

getting expression into a Group By query in oracle.

I have a simple table with two columns. 'ID' and 'Amount'

I want output of the SQL to the following (only 2 fields in the output): I have attached the desired output.

select sum(amountheld) from table1
where member_status = 'MEMBER'
group by ID

This group by query works. But how can i get the expression field (the first field which 'TEMPACCOUNT') in this query (based on my attached output).

View 5 Replies View Related

SQL & PL/SQL :: ORA-00979 - Not A GROUP BY Expression At Runtime?

Oct 13, 2010

I ran into something that has me wondering about which errors are caught at compile-time, and which are caught at runtime. There is a pretty big package with lots of complicated queries that heavily use GROUP BY, and in the last couple of years I thought I never had the case of an ORA-00979 happening an RUNTIME, which is now happening.

The last heavy changes in that package did I make in the Oracle 9.2 days, and I *believe* (although my memory might be wrong) that back then the ORA-00979 errors came up at compile time.

So is it normal that the error doesn't show up during compile time, only during runtime, or should it be considered a bug?

SQL> CREATE TABLE test_tab(a NUMBER(1), b NUMBER(1));

Table created.

SQL> INSERT INTO test_tab VALUES (1,1);

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE test_prod AS
2 v_a NUMBER;
3 v_b NUMBER;
4 BEGIN
5
6 SELECT a,b
7 INTO v_a, v_b
8 FROM test_tab
9 GROUP BY a;
10
11 END;
12 /

Procedure created.

SQL>
SQL> BEGIN
2 test_prod;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "PFK.TEST_PROD", line 6
ORA-06512: at line 2

View 5 Replies View Related

SQL & PL/SQL :: Can Group BY Clause Have Expression Field

Apr 4, 2011

Can Group BY clause have an expression field?

Say for example, I want to group by the fields "Type" and and expression called "expression" (which is a case statement). I tried running this query and it says "expression" is invalide identifier.

select type, case when SUBSTR (glnumber, 1, 2) = '05' then 'IS'
when SUBSTR (glnumber, 1, 2) = '06' then 'IS'
else 'BS'
end "expression" , sum(balance)
from table
group by TYPE, "expression"

If there is any online material on how to GROUP BY on an expression(like above)

View 5 Replies View Related

JDeveloper, Java & XML :: Missing Expression And Then Group Function Not Allowed Error

Feb 23, 2011

I have 2 tables used in this problem: ODETAILS and ORDERS.

ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST

UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);

In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.

View 3 Replies View Related

Forms :: Pivot Table Inside Group

Mar 21, 2011

I want to use the pivot table inside the group.

View 3 Replies View Related

Server Administration :: Organize Or Group Schemas Inside Instance?

Jul 8, 2011

I have a lot of shemas inside an instance... How to organize them?

What I see is a long list of schemas which could be difficult to manage if number of schemas grows up... For example, I'd like to group Schema_1, Schema_2 and Schema_3 in something like Group_1, so it could be seen more organized.

Group_1
Schema_1
Schema_2
Schema_3

Sometimes a java or .net application uses 3 or 4 schemas... these schemas are in the same instance which has other schemas for other applications.

View 2 Replies View Related

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

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

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