CODEWITHSUNDEEP
sqloracle-database
Auntie Anita
Auntie Anita

Reputation: 59

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

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!

Upvotes: 0

Views: 466

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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:

  1. It seems you have forgotton SZRATND_CRN in your ON clause.
  2. You outer join SZRATND, but you don't use it anywhere in your query. You can just as well remove this completely.
  3. 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.
  4. 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).
  5. 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.

Upvotes: 1

Related Questions