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
ADVERTISEMENT
Jun 15, 2012
I have a table like below:
COLUMN TYPE
USER_ID VARCHAR2 (10 Byte)
PROCESS_ID VARCHAR2 (30 Byte)
END_TIME DATE(STAMP)
TO_LOC VARCHAR2 (12 Byte)
TO_LOC_TYPE VARCHAR2 (15 Byte)
FROM_LOC VARCHAR2 (12 Byte)
ITEM_ID VARCHAR2 (25 Byte)
CASES NUMBER (12,4)
LMS_UDA1 VARCHAR2 (250 Byte)
ZONE VARCHAR2 (2 Byte)
I only want get one record with all columns, only have one clause MAX(END_TIME) But the other column have difference value. when i use MAX(END_TIME) and GROUP BY USER_ID,PROCESS_ID,CASES,... the sql didnot give one record, It give many records
View 6 Replies
View Related
Jun 18, 2012
I have table which contains huge data. around 12 lakhs records. when I use sum function on accountname and docdate it gives wrong value. once I restart the server it gives the correct value. one or two days it gives correct value after that again I get the same problem. If I restart again it gives correct value.
I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 64 bit server on Linux.
View 6 Replies
View Related
Feb 12, 2012
I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups.
CREATE TABLE target_data
(
T_LINK VARCHAR2(50)
, t_mon varchar2(6)
, t_grp varchar2(30)
, t_views NUMBER
[code]...
When I run this, I get output as
t_grp count_80 goal
grp3 1 0.1
But I want the output as
t_grp count_80 goal
grp3 1 0.1
grp2 1 0.1
grp1 ... ...
grp0 ... ...
View 16 Replies
View Related
Nov 16, 2012
I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as
select asigno,count(*) from angdata77 group by asigno order by asigno;
Is there any other query for retrieving the data from angdata77
View 5 Replies
View Related
Jul 26, 2013
I am trying to write a proper query to fetch data from database. Scenario:
I need to retrieve employees who are not working in multiple departments. scott@TESTCRM> select * from emp1;
EMPNO DEPTNO
---------- ----------
7654 30 7698 30 7788 20 7788 30 7876 20 7900 10 7900 30 7902 20 7934 10
scott@TESTCRM>
Ouput Expected is
EMPNO DEPTNO
---------- ----------
7654 30 7698 30 7876 20 7902 20 7934 10
View 9 Replies
View Related
Mar 28, 2013
formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.
create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));
--table rel_data_mf data as comma separated values
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U
[Code]...
--Required output
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U
View 6 Replies
View Related
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
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
Dec 14, 2010
I have a report with 2 groups Gheader and Glines.The report looks at PO headers and lines. I want to create a data link from the the 2 queries based on the line id in po_lines_all.However I only want to select this in the lines query so I do not get repeating records at the header query.
View 11 Replies
View Related
Jan 8, 2011
Actually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.
View 6 Replies
View Related
Jun 19, 2012
I have 11.1.0.7 instance running and got more 300G of storage available for the database. I would like to know what are the steps to add these disks/storage to the existing DATA asm disk group?
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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