Skip to content
Advertisement

Using Oracle SQL to join duplicate counts to an existing subquery

I have been asked to build a query that runs quality checks for data on a certain state test.

I have the following query that creates a variable named ‘Flag’ to identify cases where there are issues. The main query is really in a subquery, and I filter out all records where there isn’t a flag by only selecting cases where Flag has a value.

I would like to be able to have a CASE WHEN for instances where there are duplicate tests. We do have some duplicates where, for the same student ID (test.studentID, studenttestscore.studentID, students.ID), a student will have the same test date for the same exam.

Test date, as you can see, is STUDENTTEST.TEST_DATE. Exams can be identified by either STUDENTTEST.ID or TEST.NAME. It might be easier to look for records where STUDENTTEST.test_date and STUDENTTEST.ID are the same for a STUDENTTEST.studentID. But once you get that count, it would have to be appended to the subquery here so that we can have a student name associated with it.

Thank you so much!!

Advertisement

Answer

From what I understood you can add this analytical count() to your case:

Here is the example:

Result:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement