Group by

asaini · #1 02-16-2010, 12:53 AM

Hi There,

I have 2 tables as below:

TABLE1
FirstnameSurname DOB File no
A Smith 1/04/1980 122
A Smith 1/04/1980 200
B Adam 2/04/1988 110
C Smith 12/04/1985 250
D Miles 12/12/1945 300
D Miles 12/12/1945 400

TABLE2
File no Order date Delivery date
122 14/10/1950 14/10/1950
200 25/01/1960 2/11/1990
110 25/04/1999 25/04/1999
250 25/01/1980 11/11/1991
300 22/01/1978 23/04/1987
400 14/07/1974 25/01/1982

I want to get the cases where we have same surname,first name& DOB but only one of such file has the same order date & delivery date. So the results should look something like:
File A FileB
122 200

Any help will be greatly appreciated

PAVB · #2 02-16-2010, 06:35 AM

... and the specific question is?

asaini · #3 02-18-2010, 04:16 AM

I want to find the case such as

File A FileB
122 200

m not sure how to get this result

aryasen · #4 03-04-2010, 06:36 AM

Pls try this one ...this will give the correct result vertically.if you use pl\sql you will get the exact result.

select tab2.fileno
from ( select * from table2 where orddate=del_date ) tab1 right outer join
( select fileno
from table1
where firstname in ( select firstname
from table1
group by firstname,surname,dob
having count(fileno)>1)
) tab2 on (tab1.fileno=tab2.fileno)

PAVB · #5 03-04-2010, 05:07 PM

Quote:

Originally Posted by aryasen

this will give the correct result vertically.

really?

lets see...

Code:

SQL> 
SQL> create table TABLE1
  2  (
  3  Firstname varchar2(10),
  4  Surname   varchar2(10),
  5  DOB       date,
  6  File_no   number);

Table created.

SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 122);

1 row created.

SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 200);

1 row created.

SQL> insert into TABLE1 values('B', 'Adam',  to_date('02/04/1988','dd/mm/yyyy'), 110);

1 row created.

SQL> insert into TABLE1 values('C', 'Smith', to_date('12/04/1985','dd/mm/yyyy'), 250);

1 row created.

SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 300);

1 row created.

SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 400);

1 row created.

SQL> commit;

Commit complete.

SQL> create table TABLE2 
  2  (
  3  File_no number,
  4  Order_date date,
  5  Delivery_date date);

Table created.

SQL> insert into TABLE2 values(122, to_date('14/10/1950','dd/mm/yyyy'), to_date('14/10/1950','dd/mm/
yyyy')); 

1 row created.

SQL> insert into TABLE2 values(200, to_date('25/01/1960','dd/mm/yyyy'), to_date('02/11/1990','dd/mm/
yyyy'));

1 row created.

SQL> insert into TABLE2 values(110, to_date('25/04/1999','dd/mm/yyyy'), to_date('25/04/1999','dd/mm/
yyyy'));

1 row created.

SQL> insert into TABLE2 values(250, to_date('25/01/1980','dd/mm/yyyy'), to_date('11/11/1991','dd/mm/
yyyy')); 

1 row created.

SQL> insert into TABLE2 values(300, to_date('22/01/1978','dd/mm/yyyy'), to_date('23/04/1987','dd/mm/
yyyy')); 

1 row created.

SQL> insert into TABLE2 values(400, to_date('14/07/1974','dd/mm/yyyy'), to_date('25/01/1982','dd/mm/
yyyy')); 

1 row created.

SQL> commit;

Commit complete.

SQL> select tab2.fileno
  2  from ( select * from table2 where orddate=del_date ) tab1 right outer join 
  3  ( select fileno
  4  from table1 
  5  where firstname in ( select firstname
  6  from table1
  7  group by firstname,surname,dob
  8  having count(fileno)>1) 
  9  ) tab2 on (tab1.fileno=tab2.fileno) 
 10  ;
( select fileno
         *
ERROR at line 3:
ORA-00904: "FILENO": invalid identifier


SQL>

leason learned? ... you have to test your stuff, people asking question already have their problems; please do not add new ones.

aryasen · #6 03-04-2010, 11:09 PM

Quote:

Originally Posted by PAVB

really?

lets see...

Code:

SQL> 
SQL> create table TABLE1
  2  (
  3  Firstname varchar2(10),
  4  Surname   varchar2(10),
  5  DOB       date,
  6  File_no   number);

Table created.

SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 122);

1 row created.

SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 200);

1 row created.

SQL> insert into TABLE1 values('B', 'Adam',  to_date('02/04/1988','dd/mm/yyyy'), 110);

1 row created.

SQL> insert into TABLE1 values('C', 'Smith', to_date('12/04/1985','dd/mm/yyyy'), 250);

1 row created.

SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 300);

1 row created.

SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 400);

1 row created.

SQL> commit;

Commit complete.

SQL> create table TABLE2 
  2  (
  3  File_no number,
  4  Order_date date,
  5  Delivery_date date);

Table created.

SQL> insert into TABLE2 values(122, to_date('14/10/1950','dd/mm/yyyy'), to_date('14/10/1950','dd/mm/
yyyy')); 

1 row created.

SQL> insert into TABLE2 values(200, to_date('25/01/1960','dd/mm/yyyy'), to_date('02/11/1990','dd/mm/
yyyy'));

1 row created.

SQL> insert into TABLE2 values(110, to_date('25/04/1999','dd/mm/yyyy'), to_date('25/04/1999','dd/mm/
yyyy'));

1 row created.

SQL> insert into TABLE2 values(250, to_date('25/01/1980','dd/mm/yyyy'), to_date('11/11/1991','dd/mm/
yyyy')); 

1 row created.

SQL> insert into TABLE2 values(300, to_date('22/01/1978','dd/mm/yyyy'), to_date('23/04/1987','dd/mm/
yyyy')); 

1 row created.

SQL> insert into TABLE2 values(400, to_date('14/07/1974','dd/mm/yyyy'), to_date('25/01/1982','dd/mm/
yyyy')); 

1 row created.

SQL> commit;

Commit complete.

SQL> select tab2.fileno
  2  from ( select * from table2 where orddate=del_date ) tab1 right outer join 
  3  ( select fileno
  4  from table1 
  5  where firstname in ( select firstname
  6  from table1
  7  group by firstname,surname,dob
  8  having count(fileno)>1) 
  9  ) tab2 on (tab1.fileno=tab2.fileno) 
 10  ;
( select fileno
         *
ERROR at line 3:
ORA-00904: "FILENO": invalid identifier


SQL>

leason learned? ... you have to test your stuff, people asking question already have their problems; please do not add new ones.

============

Pls change the column names as

1)
SQL> create table TABLE1
2 (
3 firstname varchar2(10),
4 surname varchar2(10),
5 dob date,
6 fileno number);

2)
SQL> create table TABLE2
2 (
3 File_no number,
4 orddate date,
5 del_date date);

then you try that query.. u will get the vertical result

sat_mellow · #7 03-05-2010, 01:39 AM

select dt1.fileno from dt1,
(select firstname,surname,dob
from table1 a
where a.fileno in (select b.file_no from table2 b where b.delivery_date = b.order_date)
group by firstname,surname,dob
having count(fileno)>1) dt2
where dt1.firstname = dt2.firstname
and dt1.surname=dt2.surname
and dt1.dob = dt2.dob

Please substitute your columns in the query

aryasen · #8 03-05-2010, 01:49 AM

Quote:

Originally Posted by sat_mellow

select dt1.fileno from dt1,
(select firstname,surname,dob
from table1 a
where a.fileno in (select b.file_no from table2 b where b.delivery_date = b.order_date)
group by firstname,surname,dob
having count(fileno)>1) dt2
where dt1.firstname = dt2.firstname
and dt1.surname=dt2.surname
and dt1.dob = dt2.dob

Please substitute your columns in the query

===============================

select tab2.File_no
from ( select * from table2 where Order_date=Delivery_date) tab1 right outer join
( select File_no
from table1
where Firstname in ( select Firstname
from table1
group by Firstname,Surname,dob
having count(File_no)>1)
) tab2 on (tab1.File_no=tab2.File_no)

asaini · #9 03-08-2010, 11:21 PM

Thanks Arya...it works