ORACLE SQL - SELECT SUM from One Table With Conditions in Another Table

Question

I have two tables: SZRATND and SFRSTCR.

+--------------+-------------------+-------------+-------------------+-----------------------+---------------------+
| SZRATND_PIDM | SZRATND_TERM_CODE | SZRATND_CRN | SZRATND_RSTS_CODE | SZRATND_ATTENDING_IND | SZRATND_LAST_ATTEND |
+--------------+-------------------+-------------+-------------------+-----------------------+---------------------+
|       264185 |            201480 |       82223 | RE                | Y                     |                     |
|       264185 |            201710 |       10716 | RW                | N                     | 1/16/2017           |
|       264185 |            201710 |       10943 | RW                | S                     | 4/29/2017           |
|       264185 |            201710 |       10960 | RW                | S                     | 4/29/2017           |
|       264185 |            201710 |       11362 | RW                | S                     | 4/10/2017           |
|       264185 |            201580 |       80094 | RE                | Y                     |                     |
|       264185 |            201610 |       10439 | D2                | Y                     |                     |
|       264185 |            201680 |       80769 | RW                | Y                     |                     |
|       264185 |            201680 |       82184 | RW                | Y                     |                     |
|       264185 |            201680 |       82114 | RW                | Y                     |                     |
|       264185 |            201680 |       80806 | RW                | Y                     |                     |
+--------------+-------------------+-------------+-------------------+-----------------------+---------------------+



+--------------+-------------------+-------------+-------------------+-------------------+
| SFRSTCR_PIDM | SFRSTCR_TERM_CODE | SFRSTCR_CRN | SFRSTCR_RSTS_CODE | SFRSTCR_CREDIT_HR |
+--------------+-------------------+-------------+-------------------+-------------------+
|       264185 |            201480 |       82223 | RE                |                 1 |
|       264185 |            201580 |       80094 | RE                |                 1 |
|       264185 |            201610 |       10439 | D2                |                 0 |
|       264185 |            201680 |       80769 | RW                |                 3 |
|       264185 |            201680 |       80806 | RW                |                 3 |
|       264185 |            201680 |       82114 | RW                |                 3 |
|       264185 |            201680 |       82184 | RW                |                 3 |
|       264185 |            201710 |       10716 | RW                |                 3 |
|       264185 |            201710 |       10943 | RW                |                 3 |
|       264185 |            201710 |       10960 | RW                |                 3 |
|       264185 |            201710 |       11362 | RW                |                 3 |
+--------------+-------------------+-------------+-------------------+-------------------+

The data shown is for one PIDM (person).
I need the SUM of SFRSTCR_CREDIT_HR for the rows where SZRATND_ATTENDING_IND = 'Y'
The PIDM, TERM_CODE, CRN, and RSTS_CODEs for both tables must match.

My desired and actual results FOR SZRATND_ATTENDING_IND = 'Y'

+--------------+-------------------+----+-----------------+
|              | DESIRED RESULT    |    | ACTUAL RESULT   |  
+--------------+-------------------+----+-----------------+   
| SFRSTCR_PIDM | ATTENDING_HOURS   |    | ATTENDING_HOURS | 
| 264185       | 0                 |    | 14              |
+--------------+-------------------+----+-----------------+

Code:

SELECT DISTINCT  
F.SFRSTCR_PIDM,
REG.STUDENT_REG_HRS AS STUDENT_REG_HRS,
ATTENDING.ATT_HRS AS STUDENT_ATT_HRS

FROM SFRSTCR F 
JOIN (SELECT SFRSTCR_PIDM, SUM(SFRSTCR_CREDIT_HR) AS ATT_HRS
                    FROM SZRATND ATTEND 
                    JOIN SFRSTCR  ON SFRSTCR_PIDM = ATTEND.SZRATND_PIDM
                    WHERE ATTEND.SZRATND_TERM_CODE = SFRSTCR_TERM_CODE
                      AND ATTEND.SZRATND_RSTS_CODE = SFRSTCR_RSTS_CODE
                      AND ATTEND.SZRATND_CRN = SFRSTCR_CRN
                      AND ATTEND.SZRATND_ATTENDING_IND = 'Y'
                    GROUP BY SFRSTCR_PIDM) ATTENDING ON F.SFRSTCR_PIDM = ATTENDING.SFRSTCR_PIDM

JOIN (SELECT SF.SFRSTCR_PIDM, SUM(SF.SFRSTCR_CREDIT_HR) AS STUDENT_REG_HRS
FROM SFRSTCR SF 
WHERE SF.SFRSTCR_RSTS_CODE LIKE 'R%'
AND SF.SFRSTCR_TERM_CODE = '201710'
GROUP BY SF.SFRSTCR_PIDM)REG ON F.SFRSTCR_PIDM = REG.SFRSTCR_PIDM 

WHERE 
F.SFRSTCR_RSTS_CODE LIKE 'R%'
AND F.SFRSTCR_TERM_CODE = '201710'
AND F.SFRSTCR_PIDM ='264185'

I have tried several things, but nothing seems to give me the correct results.

This code works to pull the number of hours a student is attending, but when I add the GROUP BY clause, it makes the result incorrect. This is the code that should be in the "ATTENDING join." Any ideas how I can include this?

SELECT NVL(SUM(SFRSTCR_CREDIT_HR), 0) AS ATT_HRS
    FROM SFRSTCR 
    JOIN SZRATND ATTEND  ON SFRSTCR_PIDM = ATTEND.SZRATND_PIDM
               AND ATTEND.SZRATND_TERM_CODE = SFRSTCR_TERM_CODE
               AND ATTEND.SZRATND_ATTENDING_IND = 'Y'
               AND ATTEND.SZRATND_RSTS_CODE = SFRSTCR_RSTS_CODE
               AND ATTEND.SZRATND_CRN = SFRSTCR_CRN

    WHERE SFRSTCR_PIDM ='264185'
    AND SFRSTCR_TERM_CODE = '201710'
    AND SFRSTCR_RSTS_CODE LIKE 'R%'

FINAL CORRECTED CODE:

SELECT DISTINCT
SPRIDEN_ID AS "ID",
SPRIDEN_LAST_NAME AS "LAST_NAME",
SPRIDEN_FIRST_NAME AS "FIRST_NAME",
ROBUSDF_VALUE_24 AS "FallTNP_Eligibility",
ROBUSDF_VALUE_25 AS "SpringTNP_Eligibility",
ROBUSDF_VALUE_26 AS "SummerTNP_Eligibility",
F.SFRSTCR_TERM_CODE AS "ENROLLED_TERM",
REG.STUDENT_REG_HRS AS STUDENT_REG_HRS,
ATT.ATT_HRS AS STUDENT_ATT_HRS

--CASE WHEN REG.STUDENT_REG_HRS <> ATT.ATT_HRS THEN 'LOOK' END AS "LOOK"

FROM SFRSTCR F
JOIN SPRIDEN ON F.SFRSTCR_PIDM = SPRIDEN_PIDM
LEFT OUTER JOIN ROBUSDF ON SPRIDEN_PIDM = ROBUSDF_PIDM
                    AND ROBUSDF_VALUE_36 = 'Y'
                    AND ROBUSDF_AIDY_CODE = :AidYear.AidYear
LEFT JOIN( SELECT NVL( SUM(SFRSTCR_CREDIT_HR), 0) AS ATT_HRS, SFRSTCR_PIDM AS ATT_PIDM
    FROM SFRSTCR
    JOIN SZRATND ATTEND  ON SFRSTCR_PIDM = ATTEND.SZRATND_PIDM
           AND ATTEND.SZRATND_TERM_CODE = SFRSTCR_TERM_CODE
           AND ATTEND.SZRATND_ATTENDING_IND = 'Y'
           AND ATTEND.SZRATND_RSTS_CODE = SFRSTCR_RSTS_CODE
           AND ATTEND.SZRATND_CRN = SFRSTCR_CRN
    WHERE
    SFRSTCR_TERM_CODE IN (:Term.TermCode,:Term.TermCode + 9)
    AND SFRSTCR_RSTS_CODE LIKE 'R%'
    GROUP BY SFRSTCR_PIDM
    ) ATT ON F.SFRSTCR_PIDM = ATT.ATT_PIDM

JOIN (SELECT SF.SFRSTCR_PIDM, SUM(SF.SFRSTCR_CREDIT_HR) AS STUDENT_REG_HRS
    FROM SFRSTCR SF
    WHERE SF.SFRSTCR_RSTS_CODE LIKE 'R%'
    AND SF.SFRSTCR_TERM_CODE IN (:Term.TermCode,:Term.TermCode + 9)
    GROUP BY SF.SFRSTCR_PIDM)REG ON F.SFRSTCR_PIDM = REG.SFRSTCR_PIDM

WHERE
SPRIDEN_CHANGE_IND IS NULL
AND F.SFRSTCR_RSTS_CODE LIKE 'R%'
AND F.SFRSTCR_TERM_CODE IN (:Term.TermCode,:Term.TermCode + 9)
AND ROBUSDF_VALUE_36 = 'Y'
ORDER BY SPRIDEN_ID

Thorsten Kettner, you are the best! Thank you for your patience!

You say "The PIDM, TERM_CODE, CRN, and RSTS_CODEs for both tables must match". So why don't I see ATTEND.SZRATND_RSTS_CODE = SFRSTCR.SZRATND_RSTS_CODE in your ON clause? It would alaso read better to have ATTEND.SZRATND_TERM_CODE = SFRSTCR.SZRATND_TERM_CODE instead of ATTEND.SZRATND_TERM_CODE = '201710' in the clause. (And it enhances maintainability, as you'd have to change the code in only one place, if you wanted another.) — Thorsten Kettner
– Thorsten Kettner, Commented May 17, 2017 at 20:02
It does have the same value for the RSTS_CODE of both tables. When I finally use the code in production, there will be no defined values. All values will be entered by user input. :REG_TERM for '201710', and there will be no PIDM entered. The PIMDs will be from the SELECT statement. I hope that makes sense. I can go ahead and edit it again for readability. :) — Auntie Anita
– Auntie Anita, Commented May 17, 2017 at 20:19
No. Your ON clause should say join only records with the same RSTS_CODE (i.e. RE with RE, RW with RW), but instead it says combine them with records starting with R (i.e. RE with RE, RE with RW, RW with RW, RW with RE). Please write a proper ON clause where you make sure that the columns of one table matches the columns of the other (as I've shown in my answer). You can add further criteria, but you need those four conditions at least. — Thorsten Kettner
– Thorsten Kettner, Commented May 17, 2017 at 20:24
Oh, I see, you've changed it right now. Move criteria on SFRSTCR to the WHERE clause, though. (That is AND SFRSTCR_TERM_CODE = '201710' AND SFRSTCR_RSTS_CODE LIKE 'R%'.) — Thorsten Kettner
– Thorsten Kettner, Commented May 17, 2017 at 20:25

Thorsten Kettner · Accepted Answer · 2017-05-17 10:42:31Z

1

I must admit I haven't looked much at your query. It looks so complicated while it seems you simply want to join the tables and then get the credit_hr sum per attending_ind. That would be:

select
  z.szratnd_attending_ind,
  sum(f.sfrstcr_credit_hr) as attending_hrs
from szratnd z
join sfrstcr f on  f.szratnd_pidm      = z.szratnd_pidm
               and f.szratnd_term_code = z.szratnd_term_code
               and f.szratnd_crn       = z.szratnd_crn
               and f.szratnd_rsts_code = z.szratnd_rsts_code
where z.szratnd_pidm = 264185
group by szratnd_attending_ind;

As to your modified query:

It seems you have forgotton SZRATND_CRN in your ON clause.
You outer join SZRATND, but you don't use it anywhere in your query. You can just as well remove this completely.
You inner join the total SFRSTCR_CREDIT_HR per student and :REG_TERM. This can be different from '201710' you are using in your main query. So if there is no match for :REG_TERM, you dismiss the student's records.
You are grouping by SFRSTCR_CREDIT_HR. I don't know why. This gives you two sums, one for 264185|1, one for 264185|3. And you don't even show for which group you are displaying the sum (SFRSTCR_CREDIT_HR is not in your select list).
DISTINCT is very often a sign for a badly written query. The one thing it does here is this: If you find, say, six records for 264185|1 and two records for 264185|3, then you'd get 264185|6 and 264185|6 (because 1 x 6 = 6 and 2 x 3 = 6). In such a case you'd dismiss one of the rows as a duplicate. Doesn't seem to make sense to me.

edited May 17, 2017 at 10:42

answered May 16, 2017 at 21:58

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Auntie Anita Over a year ago

I will edit my code to show you why I am having issues. What I need is for szrantnd_attending_ind = 'y'. the problem is when there is no Y, i get no results (although have have a left outer join).

Thorsten Kettner Over a year ago

Okay, I've updated my answer with comments on your altered query.

Auntie Anita Over a year ago

How can i use this w/o GROUP BY? EDIT: I included the code at the very bottom of the question. I have the code, but I just do not know how to include it in order to use it. Thanks!

Thorsten Kettner Over a year ago

Simply remove the group by clause and the z.szratnd_attending_ind, from the select list from my query. And add whatever further criteria you need (on term_code, rsts_code) to the where clause.

Auntie Anita Over a year ago

The problem is that I need to use the code to apply to each student. I am querying thousands of records to find students that meet the criteria. The one student above is just an example. In order to use the code above, I had to use it in a JOIN, but that did not seem to work because I need a PIDM to join it the rest of the code.

|

Collectives™ on Stack Overflow

ORACLE SQL - SELECT SUM from One Table With Conditions in Another Table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related