SQL help inserting a count into existing code and using as selection criteria.

I need assistance with inserting a count into some working SQL code for Satisfactory Academic Progress processing to identify graduate students who have more than two C grades. The code currently selects students who have a GPA below 3.0, attempted credits >= 15 and are not already in a warning group. The 'C' selection criteria is new.
The only output from this code is RORSTAT_PIDM. The student is selected or they are not.  

The working code is:


WHERE   SGBSTDN_TERM_CODE_EFF = (SELECT MAX(A.SGBSTDN_TERM_CODE_EFF)   --find student's current level of undergrad or graduate
                                FROM   SGBSTDN A
                                WHERE  A.SGBSTDN_PIDM = RORSTAT_PIDM
                                AND RORSTAT_AIDY_CODE = ROBINST_AIDY_CODE
                                AND    A.SGBSTDN_TERM_CODE_EFF <= ROBINST_CURRENT_TERM_CODE)

AND SGBSTDN_LEVL_CODE IN ('GR','DC')        -- select if student is in a doctoral or graduate program            
AND    SGBSTDN_LEVL_CODE = SHRLGPA_LEVL_CODE       --link levels to get the correct hours and GPA for the level                

AND SHRLGPA_GPA_TYPE_IND = 'O'  --select overall grade data

AND (NOT EXISTS  -- exclude if student is already in one of the RORSAPR groups below
        FROM   RORSAPR
        AND RORSAPR_SAPR_CODE IN ('M','MA','MG','FPACE','FB','FGPA','WGPA','WGR','WPACE','F','FG','P','PA','PG','AD','ARC','A','AS','AP','AI')

AND RORSTAT_AIDY_CODE = :AIDY                                  

The basic code for the count that works as a stand alone script is:

 select count(shrtckg_grde_code_final) as C_count
from  shrtckg
where  shrtckg_gmod_code = 'G'
and shrtckg_grde_code_final = 'C'  

If the count is >= 2 the pidm will be selected.

Please let me know if you would rather this request be put in an attachment. I worked with this group a long time ago and I can't remember how I got the quickest answer.

Thank you!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
So the current select includes the students with possible C grades ?

What you can do is add another subquery to do that SUM(), but would change it a bit.... And also depends on if you want visibility of that column or not. Are you doing a group by  RORSTAT_PIDM ?

e.g.    SUM(case when shrtckg_gmod_code = 'G' and shrtckg_grde_code_final = 'C' then 1 else 0 end) as C_Count

Now, the table isnt currently in your joined tables, or subqueries, so will need to join it somehow. Assume that PIDM is avaliable ?

The just add in another subquery:

WHERE .....

AND  2 <= (SELECT SUM(case when shrtckg_gmod_code = 'G' and shrtckg_grde_code_final = 'C' then 1 else 0 end) as C_Count    -- the CASE shouldnt be neededbut is more flexible
         from  shrtckg
         where RORSTAT_PIDM = shrtckg_pidm    -- not sure of field names....
         and  shrtckg_gmod_code = 'G'
         and shrtckg_grde_code_final = 'C' )

or you could check "2 > " depending on wanting to include or exclude

If you could include a code block, and the version of SQL Server, we can probably make it a lot more efficient for you with actual JOINS
DMKetcherConsultantAuthor Commented:
Hi Mark, I am attaching a file that shows how I interpreted your recommendation and inserted into my code. The error message is described. The output from this code is merely a rorstat_pidm or ID number for the student.
DMKetcherConsultantAuthor Commented:
Greetings! This did not work but it may be because I did not code it they way you would have. I am attaching the entire code. I got this small bit of code to deliver the correct count. I just need to use the count in selection criteria in the same script.

The software running behind this reads a database for student pidms and then uses this script to further select the students for a failed satisfactory academic progress code.

Select the next student pidm
where the student is a graduate student
        and has >= 2 C grades    --like '%C%'

The following code works to give the accurate count. The count needs to keep or eliminate the student.

         AND SHRTCKG_PIDM = 10001100

THe code I developed to use your solution is attached.
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

Mark WillsTopic AdvisorCommented:
Missing a SELECT

dives straight into :    or  2 >= (SUM(
and should be :        or 2 >= ( SELECT SUM(
Mark WillsTopic AdvisorCommented:
Also noticed you are using BIND VARIABLES (a variable prefixed with a colon)


Are you using SQL Server - or - another variant of SQL ?

Not that it matters for this specific question, just that syntax isnt really supported in MS SQL SERVER, like it is in Oracle or SQL*Plus

It might help you when asking questions to include the version / variant of SQL you are using....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DMKetcherConsultantAuthor Commented:
Thank you Mark! I finished testing this morning and it works well. Janis
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.