SQL & PL/SQL :: Group Functions In Joins

Jul 29, 2013

i want to get SUM(salary) by combining both employee and employees table.Look my table structure below:

SQL> select * from employee;

EMPNO ENAME HIREDATE ORIG_SALARY SALARY R MGR DEPTNO
---------- --------------- --------- ----------- ---------- - ---------- ----------
1 Jason 25-JUL-96 1234 8767 E 2 10
2 John 15-JUL-97 2341 3456 W 3 20
3 Joe 25-JAN-86 4321 5654 E 3 10
4 Tom 13-SEP-06 2413 6787 W 4 20
5 Jane 17-APR-05 7654 4345 E 4 10
6 James 18-JUL-04 5679 6546 W 5 20
7 Jodd 20-JUL-03 5438 7658 E 6 10
8 Joke 01-JAN-02 8765 4543 W 20
9 Jack 29-AUG-01 7896 1232 E 10
[code]....

Above, i used separate queries to get the result of SUM(salary) by deptno.Here, I want a single query to get SUM(salary) with deptno.

deptno Sum(salary)
----------------------------
10 30056
20 27132
30 6300
40 4300

View 4 Replies


ADVERTISEMENT

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

PL/SQL :: Analytical Functions In Group By

Sep 27, 2012

I have simplified this for ease of understanding. I have a Data column and a Month_ID column like this:

Values Month_ID
--------- -------------------------------------------------------
AAA 1
BBB 2

I split this out to values per year like this

Value_2011 Value_2012 Month_ID
-------------------------------------------------------------------------
AAA 1
BBB 2

Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?

I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?

View 2 Replies View Related

SQL & PL/SQL :: Group Functions - Number Of Employees Joined

Aug 21, 2013

I am having a table employees with columns

1.employee_id
2.department_id
3.hire_date

Display department ID, year, and Number of employees joined?

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

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

Self Joins Getting Rid Of Duplicates?

Jun 17, 2008

I have a table1:
userid, name, town.

Now i want to do a self join like:

select a.name, b.name, a.town
from table1 a
inner join table1 b
on a.town = b.town
where a.first_name <> b.first_name AND
a.last_name <> b.last_name

i added the where clause to limit duplicates i would get but i still get duplicates eg. A B london, B A london etc.

View 9 Replies View Related

Using Subqueries In JOINS

Sep 30, 2011

In mys store procedure I am using a subquery with INNER JOIN. This subquery calls a user defined function which takes main query fields as parameter values. But i am facing issue for accessing main query fields. my query is like below:

SELECT
ID, Name, sub.Desc, sub.Date
FROM MainTable main
INNER JOIN
(
SELECT * FROM RMF_GetData(main.ID)
) sub
ON main.ContactID = sub.ContactID

I need data from a function in a table format based on some case conditions. Hence i need to join it with main table. But here oracle gives error as "invalid identifier" for main.ID parameter.

View 1 Replies View Related

Query To Use Joins

Mar 11, 2009

SELECT so.* FROM shipping_order so WHERE (so.submitter = 20)
OR (so.requestor_id IN (SELECT poc.objid FROM point_of_contact poc WHERE poc.ain = 20))
OR so.objid IN (SELECT ats.shipping_order_id FROM ac_to_so ats WHERE (ats.access_control_id IN (selectac. objid FROM access_control ac WHERE ac.ain = 20
OR ac.group IN ('buyers', 'managers'))))

rewrite this query to use joins. That would greatly simplify my sql query building code. The ids, objids, submitter, ain are numeric and group is a varchar.

View 1 Replies View Related

SQL & PL/SQL :: Joins With COUNT

Nov 16, 2011

I pulled in 1121 SSN's into a table and am using that table as the basis for returning data from other tables...including how many documents a user has in their folder.

My query; however, is only returning 655 rows...it is returning only those rows that have documents in their folders. I want to return ALL rows...WHETHER OR NOT THEY HAVE A DOCUMENT COUNT (count(*)). How can I get all 1,121 rows to return? I would like the output to look like:

SSN LOCATION EMP_STATUS FOL_STATUS COUNT(*)
-- For those folders containing documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER 12

-- For those folders containing 0 documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER NULL

Here is the query in it's current state:

-- Get User/Folder/Doc Count Information
SELECT b.ssn,
b.location,
b.emp_status,
c.fol_status,
COUNT (*)

[Code]....

So again, my problem here is that...not all FOLDERS contain DOCUMENTS...but I still want the folder data lised...I just need it listed with either a zero count (0), or a NULL in the COUNT(*) column.

I'm trying the various joins, but none of them seem to be working.

I've tried the old 8i (+) join as follows:

AND c.fol_id = d.doc_fol_id(+)

I've tried the inner join:

AND c.fol_id(+) = d.doc_fol_id

...and I've tried the 9i method (left outer and full outer) using the following types of notations:

folder c full outer join documend d on c.fol_id = d.doc_fol_id

...so far, no luck. I'm still having only 655 rows returned (the 655 are those folders that HAVE document count > 0. Any folder that has zero documents in the document table just aren't being returned in the query.)

View 9 Replies View Related

Can't Get Joins On Tables Properly

May 2, 2008

why how ever way i try i cant get the joins on the tables properly.... well i know i have to work hard....if join is not proper the data i extract is also not proper.Well now i have 3 tables...

ps_operations

Name Null? Type
----------------------------------------- -------- --------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
OPERATION_NBR NOT NULL VARCHAR2(10)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
OPERATION_DESC NOT NULL VARCHAR2(70)
HOURS_PER_PIECE_QTY NOT NULL NUMBER(9,6)
PIECES_PER_HOUR_RATE_QTY NOT NULL NUMBER(15,7)
EFFECTIVE_TO_DT DATE
EXTRACT_IND VARCHAR2(1)
[code]...

I have never worked on CPK and UK....so i dont know how to use them to join the tables,.

View 11 Replies View Related

Remote DB Query With Joins?

Feb 23, 2012

We have access to a remote Oracle database in Germany and need to insert selected data to our local Oracle database. Our problem is that we have to do several joins (7 tables) on the remote database as well as using one where clause (always the same: P.T_LIEF_LFNT_1='12803193').

Unfortunately we do not have rights to create a view on the remote database. Is there another way to send the entire query to the remote database for processing? Also, the query returns approximately 34,000 rows.

Here is our current query:

INSERT INTO PRIMUS(PARTNO,
SORT_FORMAT,
TP_WORKSPACE,

[Code].....

View 3 Replies View Related

SQL Tuning - String Of One-to-one Joins

Oct 1, 2013

I have a query I am trying to tune. It presently takes anywhere from 15 minutes to two hours to run, depending on how many records the client has. But it needs to run several hundred times, and finish over the course of a weekend. When it runs over, we have problems.

Here's the basic structure of the query:

CODESELECT ...
  FROM main_tab,
       tab_a,
       tab_b,
       tab_c,
       ...
       tab_z
WHERE main_tab.client_id = :1
   AND main_tab.unique_id = tab_a.unique_id(+)
   AND main_tab.unique_id = tab_b.unique_id(+)
   AND main_tab.unique_id = tab_c.unique_id(+)
   ...
   AND main_tab.unique_id = tab_z.unique_id(+);

All of the tables are indexed (and statistics are gathered) on the field unique_idMain_tab has an index on client_id.There is a one-to-one join (sometimes one-to-zero, thus the outer join) from the main_tab table to all the other tables.These are static tables, they're wiped and recreated - no changes, inserts, deletes.

By default, the optimizer does a full table scan and then a hash join on every single of the 26 tab_a through tab_z tables, only using the index on main_tab.

By the way I can add indexes, possibly even to the point of adding an index on some tables that would include all the fields found in the select clause on that table. But I cannot change the table structure (by, say, combining these tables together).

View 2 Replies View Related

SQL & PL/SQL :: Using NVL2 To Control Joins

Jan 5, 2012

I am trying to control which tables are joined based on a null value, so I figured I could use the NVL2.

Here is the code as it stands

NVL2(TBL_TRANSACTIONS.EQUIPSVCSEQ, TBL_EQUIPANDSERVICES.LASTWORKORDERSEQ, TBL_TRANSACTIONS.WORKORDERSEQ)
= TBL_WORKORDERS.WORKORDERSEQ (+)

To explain, if first value is null, then TBL_EQUIPANDSERVICES.LastWOSeq = tbl_workorders.workorderseq (+), otherwise join the transaction.workorderseq.

When I try and execute this code, I'm given "ORA-01417: a table may be outer joined to at most one other table." I've double checked, TBL_Workorders is not joined with any other table in any select.

Now, the EquipandServices.workorderseq is joined (+) with another table.

View 5 Replies View Related

SQL & PL/SQL :: Table Partition Using Joins

Mar 21, 2011

can i able to partition the table based on the column which is in another table ??

For example Table X need to be partitioned based on the column in The Table Y . and Table X and table Y has some relation.

View 2 Replies View Related

SQL & PL/SQL :: Selecting First Row From Multiple Joins?

Apr 2, 2010

I'm putting together a path to select a revision of a particular novel:

SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s

[code]...

My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.

When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.

One other challenge is the format of the revision- a revision sequence could look like this:

A
B
C1
C2
C
D
E1
E

So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:

A1
A
B1
B

I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).

View 12 Replies View Related

Inline Query And Joins

Jul 31, 2012

When to use inline query and to use join.

View 3 Replies View Related

SQL & PL/SQL :: Primary Key / Foreign Key Joins

Sep 29, 2010

I am working on a new project in OBIEE. I am asked to do the data modeling in the database using oracle sql developer. I have to create the joins based on the requirements. I have the tables created already. But the primary keys for few tables are not defined for few tables. PK-FK joins are also not done properly.

My questions are
(1) If I have to define the primary key for the existing tables can I do that using the alter table command or should I create the table all over again and then define it?
(2) If I have to make the changes in the existing PK-FK joins how do i go about doing that?

View 11 Replies View Related

SQL & PL/SQL :: Purpose Of Using Joins Over Where Clause

Feb 29, 2012

If a query can be written using where clause, what is the purpose of using joins over where clause?

View 11 Replies View Related

SQL & PL/SQL :: NULL In Left Joins

Jun 11, 2013

I am observing some skewed results for Left outer join where the main table has NULL in the field we are joining against with another table.

Just wondering if there are some tricks to get over it. I am currently using NVL(tab1.col1,'X') = NVL(tab2.col3,'X') and am just wondering if there is a better way to handle this.

View 1 Replies View Related

PL/SQL :: Query Using Subquery And Joins

Feb 7, 2013

I want to display first joined and last joined employee name(first_name) in each department like this

department_name | first joined employee | last joined employee

I think we have to use joins and sub query.

employee table attributes -
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Convert Oracle Joins To Ansi?

Apr 11, 2011

the below merge statements has outer join.

merge into merge_st t
using (select * from merge_st1) src
on (t.id=src.id(+))
when matched then update set name=src.name;

I need to convert oracle joins to ANSI joins. I have tried below

update (select t1.name as t1_name,t2.name as t2_name
from merge_st t1 lef outer join merge_st t2
on(t1.id=t2.id))
set t1_name=t2_name;

It statements shows error like cannot modify the non key preserved tables.I have cheked these table has contains whether primary key or not.there is no constraints for these tables.Our application, constaints handle in front end. so we cannot create any constraints in oracel database.how to convert oracle joins to ansi join?

View 4 Replies View Related

SQL & PL/SQL :: Convert Scalar Queries To Joins?

Jun 20, 2012

I have the scenario like below:

create table test_a (id number, b varchar2 (20));
create table test_b (id number, a number, b number, c number, d number, e number, f number);
insert into test_a values (1,'Manu');
insert into test_a values (2,'Tanu');
insert into test_a values (3,'Anu');

[code].....

convert the query above using joins instead of scalar queries, as scalar queries decreasing the performance.

View 7 Replies View Related

SQL & PL/SQL :: Joins On Two Tables With 10 Million Rows Each

Jun 11, 2010

We have two tables, TableA and TableB that contain list of accounts and balances.The requirement is to compare the balances of accounts in both the tables, and if there is a difference, then record that difference with account number in another table.

Both TableA and TableB contain more than 10 million rows.What is the best way to do this task in PL/SQL? A join on TableA and TableB to know the differences has become very slow due to large volume.

View 20 Replies View Related

SQL & PL/SQL :: Use Only SQL Joins Not Minus Or Subquery Or Any Function

Aug 2, 2011

i have two tables having some fields .In table table1 there is 2 lakh rows and in table table2 there is 3 lakh rows.In both table 2 lakh rows are common.I have to find those 1 lakh rows which are distinct but the condition for this i have to use only sql joins not minus or subquery or any function.

when i doing this.select t2.id,t2.name,t2.loc from table1 t1,table2 t2 where t1.id <>t2.id;

View 8 Replies View Related

PL/SQL :: Nested Loops On Outer Joins

Apr 10, 2013

I have a select query that was working with no problems. The results are used to insert data into a temp table.

Recently, it would not complete executing. The explain plan shows a cartesian. But, there could be problems with using nested loops on the outer join.

Interestingly, when I copy production code and rename the temp table and rename the view, it works.

CREATE TABLE "CT"
( "TN" VARCHAR2(30) NOT NULL ENABLE,
"COL_NAME" VARCHAR2(30) NOT NULL ENABLE,
"CDE" VARCHAR2(5) NOT NULL ENABLE,
"CDE_DESC" VARCHAR2(80) NOT NULL ENABLE,

[Code]....

View 1 Replies View Related

Massive Delete (2 Joins Required) With No Partitioning

Oct 25, 2011

I'm experiencing some infinite loop for my delete. I tried many way to deal with this problem but still take too much time. I will try to be clear as possible.

I have 4 implicated table in this problem.

The deletion is done depending of the pool_id given

Table 1 contain the pool_id
Table 2 the ticket_id foreign join ticket_pool_id with the pool_id
Table 3 ticket_child_id foreign join ticket_id with the ticket_id
Table 4 ticket_grand_child_id foreign ticket_child_id join with the ticket_child_id

Concerned count for each
table 1---->1
table 2---->1 200 000
table 3---->6 300 000
table 4---->6 300 000

So in fact it`s 6.3M+6.3M+1.2M+1 row to be deleted

Here`s the constraint :
-No partintionning
-Oracle version 9
-Online all the time so no downtime neither CTAS
-We cannot use cascade constraint
-The normalization is very important

Here`s what I tried:
-Bulk delete
-Delete with statement (In and Exists clause)
-temp table for each level and 1 level join
-procedure and commit each 20k

None of those worked in a decent time frame like less then one hour. The fact that we cannot base a delete on one of the column value is not working. Is there a way I'm getting desperate now

View 3 Replies View Related

SQL & PL/SQL :: Joins - Find Missing Records Between Two Tables?

Mar 7, 2010

I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.

I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.

For missing records in the sense for records which are present in A but not in B, am using

Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL

But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones

View 5 Replies View Related

SQL & PL/SQL :: How To Implement Pagination For Large Table Joins

Sep 2, 2011

I have two large tables(rptbody and rpthead) which has over millions or even more records. Below is the table schema

describe rpthead
Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER
[code]...

What I want is getting all data if the referenced RPTNO belongs to a particular product_id from rptbody table, here's the sql

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0
WHERE
(
t0.RPTNO IN
(
SELECT t1.RPTNO FROM RPTHEAD t1 where t1.PRODUCT_ID IN ('4647')
)
)
ORDER BY t0.LINENO

Since the result set is pretty large, so my application(think it as c couple of jobs, each job should be finished in a time window) can only process a subset of all data, so I need pagination so that the next job can continue the processing until all data is processed, below is the SQL with pagination

select * from (
select a.*, ROWNUM rnum from
(
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0
WHERE
(
[code]....

As you can see each query will take 100 rows from the db. The problem for now is that the query taking too much of time(10+ mins), I know the slowness is due to "ORDER BY t0.LINENO", but it's required for pagination.

View 4 Replies View Related







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