Solved

Oracle, resultset should return only counts  of more than 1

Posted on 2009-07-13
32
299 Views
Last Modified: 2013-12-19
I'm trying to return only rows for people that have a count of more than 1 row with matching criteria. My problem is if I use the 'having count' I cannot display columns that i want to see.
Sample data is below. I do not want to see 123456 data as this person does not have more than 1 row matching criteria:
SQL> select a.person_id, b.class_code, a.school_code, a.end.date
  2  from student_program_class_tracks a, school_classes b
  3  where a.person_id in ('123456','1234567')
  4  and a.school_code = b.school_code
  5  and a.school_year = '20082009'
  6  and a.end_date > '01-JUN-2009'
  7  and a.school_year = b.school_year
  8  and a.class_code = b.class_code
  9  and b.class_homeroom_flag = 'p'
 10  order by 1,2,3,4
 11  /

PERSON_ID     CLASS_CODE       SCHOOL   END_DATE
------------- ---------------- -------- ------------------------
123456    RMA120B          804      26-JUN-09
123456   RMOFFICEB        804      26-JUN-09
1234567   RMA125B          804      26-JUN-09
select a.person_id, b.class_code, a.school_code, a.school_year

from student_program_class_tracks a, school_classes b

where a.school_code = b.school_code

and a.school_year = '20082009'

and a.end_date > '01-JUN-2009'

and a.school_year = b.school_year

and a.class_code = b.class_code

and b.class_homeroom_flag = 'p'

and a.person_id in (select t.person_id

                                               from student_program_class_tracks t

                                               where  t.person_id = a.person_id

                                               and t.school_code = a.school_code

                                               and t.school_year = a.school_year

                                               and t.school_year = '20082009'

                                               and t.end_date > '01-JUN-2009'

                                               group by t.person_id

                                               having count(person_id) > 1)

Open in new window

0
Comment
Question by:guinnie
  • 13
  • 10
  • 8
32 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Assuming personid being the unique identifier...

select distinct A.* from yourtable A join
(select personid, count(personid) from yourtable group by personid having count(personid) > 1) B on A.personid = B.personid
0
 

Author Comment

by:guinnie
Comment Utility
Racimo:

  Yes, that works if you only need to see person_id. I need to dsiplay class_code , school_code and school _year also. That's where the difficulty is. For example, I can't use:

group by person_id, class_code, school_code, school_year. If I put those columns in the group by statement, I get no rows returned.

I need to know how to include these columns with the group and having statement.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Same idea...HTH

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from school_classes group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
 

Author Comment

by:guinnie
Comment Utility
Racimo:
     That would work but school_classes doesn't have person_id column. I need to match on class_code, school_year, school_code.
SQL> desc school_classes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHOOL_CODE                               NOT NULL VARCHAR2(8)
 SCHOOL_YEAR                               NOT NULL VARCHAR2(8)
 CLASS_CODE                                NOT NULL VARCHAR2(16)


 
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
In that case...

<< That would work but school_classes doesn't have person_id column. I need to match on class_code, school_year, school_code. >>
select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from school_classes group by personid having count(distinct personid) > 1) c
                  on a.school_code = c.school_code
                  and a.school_year = c.school_year
                  and a.class_code = c.class_code
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
I realize I am having some difficulties understanding what you are trying to accomplish.  What is the unique identifier of the student_program_class_tracks table?  Also please try my first proposal...
0
 

Author Comment

by:guinnie
Comment Utility
student_program_class_tracks unique identifier is person_id. However, student_program_class_tracks
also has class_code, school_year, school_code which matches to school_classes. But
school classes does not have person_id column.
The 'first proposal' does not work because person_id does not exist in school_classes.


0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<The 'first proposal' does not work because person_id does not exist in school_classes.>>
It's ok...If personid is the unique identifier of student_program_class_tracks then the join on the columns you mentionned does not seem necessary...

Could you try the query thanks...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
to clarify school classes will return all necessary results...
0
 

Author Comment

by:guinnie
Comment Utility
I have pasted result of query below:
SQL> select distinct a.person_id, b.class_code, a.school_code, a.school_year
  2  from student_program_class_tracks a
  3          inner join school_classes b
  4                    on a.school_code = b.school_code
  5                    and a.school_year = b.school_year
  6                    and a.class_code = b.class_code
  7          inner join (select person_id, count(distinct person_id) from school_classes group by pe
rson_id having count(distinct person_id) > 1) c
  8                    on a.school_code = c.school_code
  9                    and a.school_year = c.school_year
 10                    and a.class_code = c.class_code
 11  where a.school_year = '20082009'
 12  and a.end_date > '01-JUN-2009'
 13  and b.class_homeroom_flag = 'p'
 14  /
        inner join (select person_id, count(distinct person_id) from school_classes group by person_id havi
                                                                                                   
ERROR at line 7:
ORA-00904: "PERSON_ID": invalid identifier

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Sorry please try..

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from student_program_class_tracks group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
 

Accepted Solution

by:
guinnie earned 0 total points
Comment Utility
I think you are close. The only reason we go to school classes is to see if class_code = 'P' . For some reason though it returns no rows. I have paste data to show you what I expect to see. We should see
rows for person_id 0001183676.
PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
This is what I expect to see.
The result of you query is below:
SQL> select distinct a.person_id, b.class_code, a.school_code, a.school_year
  2  from student_program_class_tracks a
  3          inner join school_classes b
  4                    on a.school_code = b.school_code
  5                    and a.school_year = b.school_year
  6                    and a.class_code = b.class_code
  7          inner join (select person_id, count(distinct person_id) from student_program_class_trac
ks group by person_id having count(distinct person_id) > 1) c
  8                    on a.person_id = c. person_id
  9  where a.school_year = '20082009'
 10  and a.end_date > '01-JUN-2009'
 11  and b.class_homeroom_flag = 'p'
 12  /

no rows selected

SQL> select a.person_id, a.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a
  3  where a.person_id in ('000137546','0001186376')
  4  and a.school_year = '20082009'
  5  and a.end_date > '01-JUN-2009'
  6  order by 1,2,3,4
  7  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
000137546     CHA3U1-01        804      20082009 26-JUN-09
000137546     HRT3M0-06        804      20082009 26-JUN-09
000137546     MCR3U1-04        804      20082009 26-JUN-09
000137546     RMA125B          804      20082009 26-JUN-09
000137546     SPH3U1-03        804      20082009 26-JUN-09

11 rows selected.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Maybe the collation is case sensitive...

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from student_program_class_tracks group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'P'
0
 

Author Comment

by:guinnie
Comment Utility
Tried that too without success. Below is a query, to show data for the 2 people. I only want to dsiplay
person_id, b.class_code, a.school_code, a.school_year for those people who have more than 1 end date being > 01-JUN-2009. But I can't figure it out. The row for person_id 000137546 should not be showing up in final sql.
SQL> select a.person_id, b.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a, school_classes b
  3  where a.person_id in ('000137546','0001186376')
  4  and a.school_code = b.school_code
  5  and a.school_year = '20082009'
  6  and to_date(a.end_date) > '01-JUN-2009'
  7  and a.school_year = b.school_year
  8  and a.class_code = b.class_code
  9  and b.class_homeroom_flag = 'p'
 10  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
000137546     RMA125B          804      20082009 26-JUN-09

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
See attached.
query.txt
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
On further review, I need to remove the class_code (and perhaps the school_code) from the partition clause.
0
 

Author Comment

by:guinnie
Comment Utility
Hello Awking:

Thank you for your suggestion. I have ran the query but get error as seen in attached file.
awking-reply.txt
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
On even further review, I think it just needs to be partitoned by person_id.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you post the table structures for the student_program_class_tracks and school_classes tables and provide some sample data for them?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
There is a typo, "a.end.date" should be "a.end_date" on the second line.
0
 

Author Comment

by:guinnie
Comment Utility
Awking00:

I think you almost have it. The only thing now is I see 1 row returned when I should see both rows.
See attached file. This will show the results of your query and the query that I show the data.
I need to see both rows for person_id 0001186376.
Thank you
awking-reply2.txt
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Change "(partition by a.person_id, b.class_code, a.school_code, a.end_date order by rownum)" cnt to "(partition by a.person_id order by rownum) cnt" and see what you get.
0
 

Author Comment

by:guinnie
Comment Utility
AWKING00:

That works great. Just one more thing if you don't mind. When I take out test for just 2 person_id, I get too many rows. I only want to get persons with multiple class codes with that end_date. i.e.
I removed check for a.person_id in ('123456','1234567'). Should I put in another inner join to school_program_class_tracks ?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
I'm not sure what you mean. Can you post the query without the 2 person_id check, what the results were, and what you expected them to be?
0
 

Author Comment

by:guinnie
Comment Utility
Hello Awking:

Sorry for the delay. I will attachequery and results (only parital as the resultset is quite large).
The results are not query with this query. It is returning people with only 1 homeroom. Not sure why .
Hope you can help ?
The 2nd file shows example of people who should not be in resultset.


awking-july14-1.txt
awking-july14-2.txt
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
See attached.
query.txt
0
 

Author Comment

by:guinnie
Comment Utility
Awking00:

Thanks for sticking with this issue. Results are attached.

Also, I will be leaving for the day shortly. But I will be on tonight to respond.
awking-july-15.txt
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you post the relevant data that exists in the student_program_class_tracks and school_classes tables that provided those latest results?
0
 

Author Comment

by:guinnie
Comment Utility
Awking000:

I shall give you a sample. For the data below, only the person id of 0001183676 with class code beginning with RM should be returned. The others do not have more than 1 homeroom.

SQL> select a.person_id, a.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a
  3  where a.person_id in ('0001100193','0001100019','0001100020','0001186376')
  4  and a.school_year = '20082009'
  5  and a.end_date > '01-JUN-2009'
  6  order by 1,2,3
  7  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001100019    4H               859      20082009 26-JUN-09
0001100020    3K               853      20082009 26-JUN-09
0001100193    5C               829      20082009 26-JUN-09
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09

9 rows selected.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Okay, now I'm a little confused. When you ran the last query, we got results like the following:
0001100019    4H               859      26-JUN-09          1
0001100019    4H               859      26-JUN-09          2
0001100020    3K               853      26-JUN-09          1
0001100020    3K               853      26-JUN-09          2
0001100193    5C               829      26-JUN-09          1
0001100193    5C               829      26-JUN-09          2
0001100193    5C               829      26-JUN-09          3
0001100193    5C               829      26-JUN-09          4
Indicating that there were duplicate values for the four attributes, but now you're not showing them.
Also, what do you mean by only the 0001186376 recode with class_code beginning with RM should be returned? If the values were just
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
without the RMOFFICEB record, should the id be returned or not? If not, what is the criteria you would use to eliminate the other four records with class_codes that don't begin with RM?
0
 

Author Comment

by:guinnie
Comment Utility
Awking00:

 I'm only showing examples because the result sets can be very large. What I'm trying to say is this:
Certain all person_ids will have multiple class_codes, it is only when we go to the school_classes
table and look for those class codes that are flagged as 'P' that we know that it is a homeroom.
In the case of person_id 0001186376, the class codes that are RMA120b and RMOFFICEB are flagged as 'P' in the school_classes so we want to see them in the resultset .
Does that clarify ?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now