Solved

Exclude records that match or many instances of a value from an oracle query

Posted on 2016-10-18
4
45 Views
Last Modified: 2016-11-06
Using the query below, is it possible to exclude all instances of a Student_Number if the total instances of 'F' in STOREDGRADES.GRADE is greater than 1?  Thanks so much!!

SELECT     
STUDENT_NUMBER,
LAST_NAME,
FIRST_NAME,
STOREDGRADES.STORECODE, 
SECTIONS.TERMID, 
STOREDGRADES.COURSE_NAME, 
STOREDGRADES.COURSE_NUMBER, 
SECTIONS.SECTION_NUMBER, 
STOREDGRADES.TEACHER_NAME, 
STOREDGRADES.GRADE,  
STUDENTS.GRADE_LEVEL

FROM         
PS.STOREDGRADES STOREDGRADES
LEFT JOIN PS.STUDENTS STUDENTS ON STOREDGRADES.STUDENTID = STUDENTS.ID 
LEFT JOIN PS.SECTIONS SECTIONS ON SECTIONS.ID = STOREDGRADES.SECTIONID
LEFT JOIN PS.COURSES COURSES ON STOREDGRADES.COURSE_NUMBER = COURSES.COURSE_NUMBER

WHERE     
(STOREDGRADES.TERMID >= 2500) 
AND (STOREDGRADES.TERMID <= 2599) 
AND (STOREDGRADES.STORECODE IN ('Y1')) 
AND (STUDENTS.SCHOOLID IN (61)) 
AND (STUDENTS.GRADE_LEVEL = 10)
AND (STUDENTS.ENROLL_STATUS = 0)
AND (STOREDGRADES.GRADE IS NOT NULL)

Open in new window

0
Comment
Question by:Basssque
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points (awarded by participants)
ID: 41848641
SELECT student_number,
       last_name,
       first_name,
       storecode,
       termid,
       course_name,
       course_number,
       section_number,
       teacher_name,
       grade,
       grade_level
  FROM (SELECT student_number,
               last_name,
               first_name,
               storedgrades.storecode,
               sections.termid,
               storedgrades.course_name,
               storedgrades.course_number,
               sections.section_number,
               storedgrades.teacher_name,
               storedgrades.grade,
               students.grade_level,
               COUNT(CASE WHEN storedgrades.grade = 'F' THEN 1 END)
                   OVER (PARTITION BY student_number)
                   student_f_cnt
          FROM ps.storedgrades storedgrades
               LEFT JOIN ps.students students ON storedgrades.studentid = students.id
               LEFT JOIN ps.sections sections ON sections.id = storedgrades.sectionid
               LEFT JOIN ps.courses courses ON storedgrades.course_number = courses.course_number
         WHERE     (storedgrades.termid >= 2500)
               AND (storedgrades.termid <= 2599)
               AND (storedgrades.storecode IN ('Y1'))
               AND (students.schoolid IN (61))
               AND (students.grade_level = 10)
               AND (students.enroll_status = 0)
               AND (storedgrades.grade IS NOT NULL))
 WHERE student_f_cnt <= 1
0
 
LVL 32

Expert Comment

by:awking00
ID: 41848694
I'm not sure, but I suspect that student_name is not in the storedgrades table, so you may want to partition by storedgrades.student_id. (NO POINTS, PLEASE!)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41848774
the partitioning clause doesn't require all of the columns to be in the same table.

my guess is student id and student number can probably be used interchangeably.

however, if they can't,  the described requirement is to count Fs by student number, so that is what should drive the partitioning clause.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL query to generate xml 4 46
oracle DR - data guard failover. 18 45
Query for timesheet application 3 16
reccommendations for a free msft sql query manager? 4 26
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

809 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