I didn't design the database, but unfortunately for whatever reason that is causing the headache here (at least for me) is that they use two identifiers! SN (student number) which is not unique and ID (studentID) which is unique but not present on all the tables sometimes only SN is present, and in this case the table I am trying to join only has SN so I have to join it to the STU (student) table which has both ID and SN...so the HIS (history table) has there final grades and uses ID...the GRD (grade) table uses SN which shows present or progress report grades they want them both added up categorically (A-G) which are college prereq courses and displaying only courses with a C- or better. So I am trying to count up all the A-G courses that they've taken and presently taking....I think I have it figured out....I am using this...I don't know if there is a better way or what... but seems ok?

[p][/p]

DECLARE @ID INT = 4056102

SELECT

SUM(HISTORY) AS HISTORY,

SUM(ELA) AS ELA,

SUM(MATH) AS MATH,

SUM(SCIENCE) AS SCIENCE,

SUM(FL) AS FL,

SUM(VA) AS VA,

SUM(Prep) AS Prep

FROM

(

SELECT

COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS HISTORY,

COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS ELA,

COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS MATH,

COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS SCIENCE,

COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS FL,

COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS VA,

COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS Prep

FROM CRS

CRS INNER JOIN

HIS ON CRS.CN = HIS.CN INNER JOIN

STU ON HIS.PID = STU.ID

WHERE

STU.ID = @ID

UNION ALL

SELECT

COUNT(CASE WHEN CRS.U1 = 'A' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS HISTORY,

COUNT(CASE WHEN CRS.U1 = 'B' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS ELA,

COUNT(CASE WHEN CRS.U1 = 'C' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS MATH,

COUNT(CASE WHEN CRS.U1 = 'D' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS SCIENCE,

COUNT(CASE WHEN CRS.U1 = 'E' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS FL,

COUNT(CASE WHEN CRS.U1 = 'F' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS VA,

COUNT(CASE WHEN CRS.U1 = 'G' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS Prep

FROM CRS

INNER JOIN

GRD ON dbo.GRD.CN = dbo.CRS.CN

INNER JOIN

STU ON GRD.SN = STU.SN

WHERE

STU.ID = @ID

)r