so if I union it separates the correct values in two rows....so how to sum the rows into one and display only one?

[p][/p]

DECLARE @ID INT = 4043300

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