Aggregate Function - Combine Results

Nov 24, 2009

I have a query more or less like this:

SELECT field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY field1;

For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.

So if I have the following:
ABC1 | 5
ABC2 | 10
XYZ1 | 3

I want results like this:
ABC | 15
XYZ1 | 3

I've tried using some case statements like

SELECT CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END AS field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END;

but this just gives me
ABC | 5
ABC | 10
XYZ1 | 3

How can I combine record 1 and 2 from the last record set example above?

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: List Function (Aggregate)

Dec 13, 2010

I have one table emp with columns id , name and dept. i want query to get the list like this........

ID name did
1 ajay 10,30,40,60
2 sree 10,30,70

View 7 Replies View Related

SQL & PL/SQL :: How To Write Analytic Function Without Aggregate

Mar 8, 2012

Do we have analytic function equivalent of following?

select object_type,owner from dba_objects group by object_type,owner ;

I am trying to get a unique srno for a combination of a 2 fields - here object_type and owner

OWNEROBJECT_TYPESRNO
SYSVIEW1
SYSTABLE2
SYSPROCEDURE3
SYSTEMVIEW4
SYSTEMTABLE5
SYSTEMFUNCTION6
SYSTEMPROCEDURE7
SCOTTTABLE8
SCOTTVIEW9
.......................

also how can I get the SRNO?

I can' use sequence in the group by function and if I get equivalent analytic for above group by even then I can't write row_number as the order by gives detail record

I don't want to wrap this select inside other select

View 4 Replies View Related

PL/SQL :: XML Aggregate Function And Out Of Process Memory

Aug 8, 2012

I am getting most of the time 'out of process memory' when i run the 'ord' procedure.here i am providing the tables and the procedure.

i have 2 table orders which holds distinct values, and departments table has ordvalue column holds long string of values for a particular record from orders table. for example if the values in orders table as follows:

ord_code ord_level ordid ordstatus ord_num user utimestamp
SR11          1 2 A          101 V     SYSDATE
SR11          1 2 A          102 V     SYSDATE
SR11          1 2 A          103 V     SYSDATE
SR11          1 2 A          104 V     SYSDATE
SR11          1 2 A          105 V     SYSDATE
SR11          1 2 A          106 V     SYSDATE
SR11          1 1 B          101 R     SYSDATE
SR11          1 1 B          102 R     SYSDATE
SR11          1 1 B          103 R     SYSDATE
SR11          1 1 B          104 R     SYSDATE
SR11          1 1 B          105 R     SYSDATE
SR11          1 1 B          106 R     SYSDATE

ETC...
AT TABLE departments data will be like this

ord_code ord_level ordid ordstatus ord_num user utimestamp
SR11          1 2 A          101,102,103,104,105,106 V sysdate
SR11          1 2 B          101,102,103,104,105,106 R sysdate

from the get_ord procedure the data aggreates using the xmlelement and gets as a long string value into departments table of ord_num column.

CREATE TABLE test.orders
(
ord_CODE VARCHAR2(4 BYTE) NOT NULL,
ord_LEVEL VARCHAR2(1 BYTE) NOT NULL,
ordID NUMBER(5) NOT NULL,
ordstatus VARCHAR2(1 BYTE) NOT NULL,
ord_num NUMBER(3) not null,
user VARCHAR2(8 BYTE),
UTIMESTAMP DATE
[code]...

View 0 Replies View Related

PL/SQL :: How Database Handles Record Locking When Aggregate Function Called

Jul 17, 2012

how the DB handles record locking when an aggregate function is called? For instance:

...
select count(*)
into   v_count
from   x;

...Is there a lock maintained on table x for the duration of the transaction so no rows can be inserted or deleted?

View 4 Replies View Related

Use MAX Function Without It Being In Results?

Apr 4, 2007

i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?

View 1 Replies View Related

Function Not Returning Data (Desired Results)

Jul 14, 2010

I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.

----Create Test Table
CREATE TABLE VC_WORKINGDAYS
(
WK_ID number NUMBER(10,0),
WK_DATE DATE,
);

-- Insert test Data
INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');

SQL
----- Result Should be WHY
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today

Attached File(s)

 create_Function.zip ( 6.39K )
Number of downloads: 1

 InsertData.txt ( 84.34K )
Number of downloads: 0

View 1 Replies View Related

SQL & PL/SQL :: Combine Both Selects Into One?

May 11, 2012

Is it possible to combine both these selects into one.?

SELECT a.grouping_set_member_id,
a.service_agreement_id,
a.enrolment_group_id,
a.enrolment_group_class_id,
a.effective_date,

[code]...

Where l_service_agreement_id,l_enrolment_group_id,l_ENROLMENT_GROUP_CLASS_ID,l_effective_date
are vaues from the First SELECT.

View 2 Replies View Related

SQL & PL/SQL :: To Get Output From Table - Possible To Combine A Row?

Mar 6, 2012

To get output from a below mentioned table? can we combine a row like that?

Table: Test_Fruits

ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit
2Vegitable
4 Apple
3 Potato
-----------------------------------------
Expecting output:
Pls guid me how to get like this?
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit 4 Apple
2Vegitable 3 Potato
-----------------------------------------

View 6 Replies View Related

SQL & PL/SQL :: Combine Query Output In One Row

Aug 13, 2010

If I have below two sql statment.

select count(*) from table_a;
select count(*) from table_b;

How can I output it in one row together like below

Table_a | Table_b|
10 | 20|

give me detailed example ....

View 2 Replies View Related

SQL & PL/SQL :: Combine 2 Queries Into 1 Without Using UNION

Jun 5, 2012

SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........

View 5 Replies View Related

SQL & PL/SQL :: How To Combine 3 Decodes Together On One Line

Mar 4, 2011

I am having trouble putting all 3 of these decodes together on one line. I would like my output to return like this:

ytd_state_tax ytd_ss_tax ytd_medicare_tax
============= ========== ================
182.29 163.28 56.37

if deduct_type=6012 return sum(pdt1.deduct_amount) YTD_MEDICARE
else
if deduct_type=6020 return sum(pdt1.deduct_amount) YTD_STATE_TAX
else
if deduct_type=6010 return sum(pdt1.deduct_amount) YTD_SOCIAL_SEC

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: How To Combine Queries When WITH Clause Is Used

Jun 21, 2010

I have 4 select queries.

Query1
Query2
Query3
Query4

First Step:Combine Query1 and Query2,but the requirement is Query2 should only use the Acct_ID and Bill_ID which are output from Query1.

Second Step:Combine Query3 and Query4,but the requirement is Query4 should only use the Acct_ID and Bill_ID which are output from Query3.

Third Step: Is to now combine both the data set from First Step and Second Step.

In order to achieve my First and Second Steps I used WITH clause.

With S1 as ( Query1 ),
S2 as ( Query1 Union Query2)
Select S2.* from S1,S2
where S1.ACCT_ID=S2.ACCT_ID
AND S1.BILL_ID=S2.BILL_ID

With S3 as ( Query3 ),
S4 as ( Query3 Union Query4)
Select S2.* from S3,S4
where S3.ACCT_ID=S4.ACCT_ID
AND S3.BILL_ID=S4.BILL_ID

1. Is that approach right for achieving my First and Second step requirements ?

2. How to achieve Third Step ?

View 3 Replies View Related

SQL & PL/SQL :: Combine Two Queries To Get Full Output

Jun 8, 2011

how to combine 2 queries as attached in notepad file in order to get full output?

View 3 Replies View Related

SQL & PL/SQL :: Combine Update And Insert Command?

Jul 23, 2012

i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).

now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!

here i have some part from the original

declare
procedure add(
i_id pls_integer,

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: Combine Two Independent Select Statements?

Dec 10, 2010

ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.

1)

SELECT
COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL,
COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,

[Code]....

2)

SELECT
SUM (CHEQUE_TOTAL) CHEQUE_TOTAL
FROM RI_CHEQUE_VOUCHER_REFUND refund
INNER JOIN CH_CASE case ON (case.id = refund.id)
INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED')
WHERE
case.creation_time<= SYSDATE
AND
case.creation_time>= SYSDATE-7

Again I need to combine the resultset.So the result would look like

Customer_Email Other_Email Customer_Whitemail Other_whitemail Customer_telephone Other_Telephone Cheque_total

View 3 Replies View Related

Combine Tables With Union But Limit Number Of Records?

Sep 13, 2007

Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.

I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?

The query is:

SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Query - Combine Prefix / Sfirst Suffix1 And Last Suffix2?

Mar 5, 2013

I have the following table
PREFIXSUFFIX1SUFFIX2CodeDescription
0117default
0220unknown
011200unknown
021240unknown
025300unknown
031340unknown

[code]...

If code value is same i would like to combine prefix,sfirst suffix1 and last suffix2. So from above table i want something like the following table.

PREFIXSUFFIX1SUFFIX2CodeDescription
0117default
022410unknown
02421426UKred

View 11 Replies View Related

Complex Case Expression - Combine Two Statements To Get Desired Bucket

May 19, 2011

I have a case expression as follows:

(CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END) AS RESULTa,

Now what I need to be able to do is place those results in buckets, similar to this:

(CASE WHEN RESULTa < 0 THEN '<0'
WHEN RESULTa between -1 AND 6 THEN '<=5'
WHEN RESULTa >5 THEN '>5'
ELSE ''
END) AS BUCKETa

I understand that I can't call an alias from a previous case expresson to get these desired results and how I could combine the two statements to get the desired bucket.

View 1 Replies View Related

Does Trcsess Combine Various Trace Files In Order In Which Queries Executed

May 28, 2013

Does trcsess combine various trace files in the order in which the queries were executed?

View 3 Replies View Related

SQL & PL/SQL :: Aggregate Between Two Dates

Jul 13, 2010

I need to aggregate the data based the two dates criteria.

I have two tables

1. Table1
Number Date1 Date2
1 10-Jun-2010 30-Jun-2010
2 10-Feb-2010 30-Feb-2010
----------------------------------
2. Table2
Number Date Revenue
1 11-Jun-2010 100
1 09-Jun-2010 100
1 12-Jun-2010 100
2 11-Feb-2010 100
2 12-Feb-2010 100
2 13-Feb-2010 100
......................
......................
So on

Output:

Number Revenue Date2
1 200 30-Jun-2010
2 300 30-Feb-2010

View 14 Replies View Related

Aggregate Two Columns

Oct 25, 2011

I have two table and I want to merge them

TERMS_TABLE
ID | TERMS
309 | 'hardware'
309 | 'software'
309 | 'computer'

TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....'

Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE but If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE , like that

result should be:

NEW_TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....,hardware,software'

How can I do that ?

View 1 Replies View Related

Tuning Aggregate Queries?

May 1, 2011

I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run

kumar[size="4"][/size][color="#0000FF"][/color]kumardba

View 5 Replies View Related

SQL & PL/SQL :: Aggregate Functions On A Join

Nov 7, 2012

I am trying to Show all Customers (custId, last name, first name) who bought cars at the Arctic AND Pacific branches and Order it by CustId.

These are the tables:

VIN CARTYP BRNAME BUYERID SALEDATE
------- ------ ---------- ---------- -----------
1JJ0001 CT9015 Arctic
1NN0001 CT9014 Arctic 10010 18-mar-2012
1PP0001 CT7013 Arctic 10007 25-sep-2012
1QQ0001 CT7012 Atlantic 10014 22-dec-2009
1RR0001 CT7011 College 10013 30-sep-2012
2JJ0001 CT9015 Pacific
2NX0001 CT9014 Pacific
2PP0001 CT7013 Arctic
2QQ0001 CT7012 Arctic

[Code]...

CUSTID LNAME FNAME BALANCE_DUE
---------- ------------ ------- -----------
10001 Disney Daisy 9000.5
10010 Dupont Caryn 40000
10006 Gordon Diane 75000.5
10005 Gordon Tracy 3000.25
10013 Gross Henry 50000
10015 Hilton Kate 40000
10011 Lea Diane 12000
10003 Lopez Athena

[Code]...

No matter how I write the query, I get the message that no rows have been selected.

View 3 Replies View Related

SQL & PL/SQL :: De-Aggregate Year To Date Figures?

Apr 27, 2011

I have a table as below:

Year Month Value
2011 01 15
2011 02 26
2011 03 34

[code]...

The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:

Year Month Mth Value
2011 01 15
2011 02 11
2011 03 8
2011 04 9

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Aggregate Contiguous Range Of Days?

Oct 21, 2010

I intend to get for every client the start date and end of a contiguous range of days. Example for the same client have two records, in the first goes from day 1 to day 5 and the second from day 3 to day 9, i intend to get a record for this client where indicated that the start date is on day 1 and ending on Day 9.

SELECT 123 as CLI_ID, TO_DATE('20100101', 'YYYYMMDD') as DT_START, TO_DATE('20100105', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100208', 'YYYYMMDD') as DT_START, TO_DATE('20100321', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100219', 'YYYYMMDD') as DT_START, TO_DATE('20100228', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100227', 'YYYYMMDD') as DT_START, TO_DATE('20100405', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100901', 'YYYYMMDD') as DT_START, TO_DATE('20101013', 'YYYYMMDD') as DT_END FROM DUAL

Expected :

CLI_ID,DT_START,DT_END
123,01/01/2010,05/01/2010
123,08/02/2010,05/04/2010
123,01/09/2010,13/10/2010

View 2 Replies View Related

SQL & PL/SQL :: User-defined Aggregate Calculation For Z Score

Jun 29, 2011

I'm calculating a Z score based on some simple numerical data thus:

create table t (id number, val number);

insert into t values(1, 1795);
insert into t values(2, 1753);
insert into t values(3, 1743);
insert into t values(4, 1876);
insert into t values(5, 1848);

[Code] .....

the logic is quite simple - calculate a moving average over the previous 12 rows, and a stdev over the same window. Then subtract the prior row's moving average from the current value, and divide by the prior row's stdev.

The issue is I want to expose this logic in a BI tool (OBI EE v10g), meaning I can't use the nested analytic functions. How to achieve this logic in a single analytic pass? The sql above took about 2 minutes to write this morning, then I've spent all day looking at user-defined aggregate functions, but haven't even been able to get the first step, the moving average, working. I can understand that I can probably create an udaf to replicate the avg(val) over (order by id ROWS BETWEEN 11 PRECEDING AND 0 FOLLOWING) functionality, but I can't see how to bundle the logic for the other three steps in the calculation into this.

From what I've read, the ODCIAggregateMerge should allow me to combine different threads that can return the different values I need for the current row calculation. Is this correct?

The only example udafs I can find are either not relevant (STRAGG) or very simple (ie don't appear to invoke multiple passes). I've also had a look at the COLLECT function, but again I can't see a way to use this.

View 6 Replies View Related

SQL & PL/SQL :: Materialized View With Aggregate And FAST REFRESH

Feb 8, 2012

I'm trying to build up a materialized view with aggregate and FAST REFRESH for INSERT and UPDATE, DELETE with no success. But the web doesn't deny it ?

--build up needed schema objects

CREATE TABLE empt
( ename VARCHAR2(20),
empno INTEGER,

[Code]....

View 9 Replies View Related

SQL & PL/SQL :: SELECT Query For Displaying Aggregate Values?

Jul 1, 2011

i have table with following data.

yearquartersales Revenue
2004Q145678
2004Q287456
2004Q356732
2004Q4120986
2005Q12398
2005Q23900
2005Q36522
2005Q42763

I want the output in following way.tell me the select query for this

yearquarterSales Revenue
2004 Q145678
Q287456
Q356732
Q4120986
2004 total Sales310852
2005 Q12398

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Maintaining Aggregate Column Data Integrity?

Jun 8, 2012

What is the best practices to maintain aggregate columns? Suppose I have the following 2 tables:

DROP TABLE TEST.ORDERS_DET_T;
DROP TABLE TEST.ORDERS_T;
CREATE TABLE TEST.ORDERS_T (
ID NUMBER NOT NULL PRIMARY KEY,
ORDER_CODE VARCHAR2(100) NOT NULL,

[code]....

I want the following test script to act int the way, described in comments. Basically this means that the sum of TEST.ORDERS_DET_T.ORDER_DET_SUMA must be equal to TEST.ORDERS_T.ORDER_SUMA after transaction commits.

INSERT INTO TEST.ORDERS_T(ID, ORDER_CODE, ORDER_SUMA) VALUES(1,'FRUITS',100);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(2,1,'APPLES',40);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(3,1,'PEAT',60);
COMMIT; --SHOULD BE OK, 40+60=100

[code]....

P.S. Creating views based on ORDERS_T and ORDERS_DET_T are not an option, if the user would still be allowed to modify data in tables (as in test scenarios). This is because of current business situation, where the client has 2 teams (outsourcing for back-office solution and insourcing for web solution) that are accessing/modifying the data in the same tables.

View 6 Replies View Related







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