Help with SUM

Transact-SQL
1

Hello i've been working on this query that i when a particular derived column reaches a certain number it adds 1 for every one over. For example the threshold for History is 4 but if the sum calculates 6 then the two extra get carried over to another column in this case PREP. Here is what i have so far. Thanks

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 (CASE WHEN HISTORY > 4 THEN 1
WHEN SCIENCE > 4 THEN 1
WHEN SCIENCE > 5 THEN 1
WHEN SCIENCE > 6 THEN 1
WHEN SCIENCE > 7 THEN 1
WHEN SCIENCE > 8 THEN 1
WHEN FL > 4 THEN 1 END ) as PREP
FROM (SELECT
SUM(CASE
WHEN c.U1 = 'A' THEN 1
ELSE 0
END) AS HISTORY,
SUM(CASE
WHEN c.U1 = 'B' THEN 1
ELSE 0
END) AS ELA,
SUM(CASE
WHEN c.U1 = 'C' THEN 1
ELSE 0
END) AS MATH,
SUM(CASE
WHEN c.U1 = 'D' THEN 1
ELSE 0
END) AS SCIENCE,
SUM(CASE
WHEN c.U1 = 'E' THEN 1
ELSE 0
END) AS FL,
SUM(CASE
WHEN c.U1 = 'F' THEN 1
ELSE 0
END) AS VA,
SUM(CASE
WHEN c.U1 = 'G' THEN 1
ELSE 0
END) AS Prep
FROM CRS AS c
INNER JOIN HIS AS h
ON h.CN = c.CN
AND h.MK NOT IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC')
AND h.MK NOT LIKE '%N%'
INNER JOIN STU AS s
ON s.ID = h.PID
AND s.ID = @ID
AND s.TG NOT IN ('N', '*', 'I')
AND s.SC = @SC
WHERE (c.U1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G'))

2

can you post some sample data and the expected result ?

3

Sorry for the crappy table example
HISTORY | ELA| MATH | SCIENCE | FL | VA | PREP
5 | 7 | 8 | 7 | 7 | 6 | ?

So for History, Science and FL the threshold is 4 so every number over should carry over to Prep. In this example History is 5, 1 over that one goes to prep, Science is 7, 3 over, FL is 7, 3 over. So Prep should be 7 (1 + 3 + 3). It should also be adding to the previous subquery sum below.

So from this part if it Summed 2 for example, then the two should be added (2 + 1 + 3 + 3) = 9
WHEN c.U1 = 'G' THEN 1
ELSE 0
END) AS Prep

I did this but its still not working. i get only 2 for prep????

SUM (CASE WHEN HISTORY = 5 THEN 1
WHEN HISTORY = 6 THEN 2
WHEN HISTORY = 7 THEN 3
WHEN HISTORY = 8 THEN 4
WHEN SCIENCE = 5 THEN 1
WHEN SCIENCE = 6 THEN 2
WHEN SCIENCE = 7 THEN 3
WHEN SCIENCE = 8 THEN 4
WHEN FL = 5 THEN 1
WHEN FL = 6 THEN 2
WHEN FL = 7 THEN 3
WHEN FL = 8 THEN 4 END ) as PREP

4 
SELECT
HISTORY,
ELA,
MATH,
SCIENCE,
FL,
VA,
Prep + CASE WHEN HISTORY > 4 THEN HISTORY - 4 ELSE 0 END
     + CASE WHEN SCIENCE > 4 THEN SCIENCE - 4 ELSE 0 END
     + CASE WHEN FL > 4 THEN FL - 4 ELSE 0 END AS PREP
FROM (
    SELECT
    SUM(CASE WHEN c.U1 = 'A' THEN 1 ELSE 0 END) AS HISTORY,
    SUM(CASE WHEN c.U1 = 'B' THEN 1 ELSE 0 END) AS ELA,
    SUM(CASE WHEN c.U1 = 'C' THEN 1 ELSE 0 END) AS MATH,
    SUM(CASE WHEN c.U1 = 'D' THEN 1 ELSE 0 END) AS SCIENCE,
    SUM(CASE WHEN c.U1 = 'E' THEN 1 ELSE 0 END) AS FL,
    SUM(CASE WHEN c.U1 = 'F' THEN 1 ELSE 0 END) AS VA,
    SUM(CASE WHEN c.U1 = 'G' THEN 1 ELSE 0 END) AS Prep
    FROM CRS AS c
    INNER JOIN HIS AS h
    ON h.CN = c.CN
    AND h.MK NOT IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC')
    AND h.MK NOT LIKE '%N%'
    INNER JOIN STU AS s
    ON s.ID = h.PID
    AND s.ID = @ID
    AND s.TG NOT IN ('N', '*', 'I')
    AND s.SC = @SC
    WHERE (c.U1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G'))
) AS derived_table
1 Like
5

Thank you Scott for replying this is the output im getting from your suggestion?
HISTORY ELA MATH SCIENCE FL VA PREP
4 5 8 6 6 4 4

Should be
HISTORY ELA MATH SCIENCE FL VA PREP
5 7 8 7 7 6 7(+ whatever was summed previously)

7 for prep because 1 for history, 3 for Science, 3 for FL.

Thanks again for helping!

6

Why should HISTORY be 5?? I don't see the actual data. Check the main query -- the inner query used to create "derived_table" -- and see if it is producing correct results. The outer part of the query should definitely work fine.

7

Your right thanks for pointing that out, this is actually one part i UNION this to another query, that's where i think the issue is at? i think the CASE statement is happening after the UNION, i dont know..what do you think?

this is the entire query

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 + CASE WHEN HISTORY > 4 THEN HISTORY - 4 ELSE 0 END
     + CASE WHEN SCIENCE > 4 THEN SCIENCE - 4 ELSE 0 END
     + CASE WHEN FL > 4 THEN FL - 4 ELSE 0 END) AS PREP

FROM 

(SELECT
		SUM(CASE
			WHEN c.U1 = 'A' THEN 1
			ELSE 0
		END) AS HISTORY,
		SUM(CASE
			WHEN c.U1 = 'B' THEN 1
			ELSE 0
		END) AS ELA,
		SUM(CASE
			WHEN c.U1 = 'C' THEN 1
			ELSE 0
		END) AS MATH,
		SUM(CASE
			WHEN c.U1 = 'D' THEN 1
			ELSE 0
		END) AS SCIENCE,
		SUM(CASE
			WHEN c.U1 = 'E' THEN 1
			ELSE 0
		END) AS FL,
		SUM(CASE
			WHEN c.U1 = 'F' THEN 1
			ELSE 0
		END) AS VA,
		SUM(CASE
			WHEN c.U1 = 'G' THEN 1
			ELSE 0
		END) AS Prep
	FROM CRS AS c
	INNER JOIN HIS AS h
		ON h.CN = c.CN
		AND h.MK NOT IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC')
		AND h.MK NOT LIKE '%N%'
	INNER JOIN STU AS s
		ON s.ID = h.PID
		AND s.ID = @ID
		AND s.TG NOT IN ('N', '*', 'I')
		AND s.SC = @SC
	WHERE (c.U1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G'))

		UNION ALL

		  SELECT    SUM(CASE WHEN c.U1 = 'A' THEN 1
                                         ELSE 0
                                    END) AS HISTORY ,
                                SUM(CASE WHEN c.U1 = 'B' THEN 1
                                         ELSE 0
                                    END) AS ELA ,
                                SUM(CASE WHEN c.U1 = 'C' THEN 1
                                         ELSE 0
                                    END) AS MATH ,
                                SUM(CASE WHEN c.U1 = 'D' THEN 1
                                         ELSE 0
                                    END) AS SCIENCE ,
                                SUM(CASE WHEN c.U1 = 'E' THEN 1
                                         ELSE 0
                                    END) AS FL ,
                                SUM(CASE WHEN c.U1 = 'F' THEN 1
                                         ELSE 0
                                    END) AS VA ,
                                SUM(CASE WHEN c.U1 = 'G' THEN 1
                                         ELSE 0
                                    END) AS Prep
                      FROM      dbo.CRS AS c
                                INNER JOIN dbo.GRD AS g ON g.CN = c.CN
                                                           AND g.M2 NOT IN (
                                                              'F', 'F+', 'F-',
                                                              'D', 'D-', 'D+',
                                                              'NM', 'NC' )
                                INNER JOIN dbo.STU AS s ON s.SN = g.SN
                                                           AND s.ID = @ID
                      WHERE     c.U1 IN ( 'A', 'B', 'C', 'D', 'E', 'F', 'G' )
					  
		) AS r