Building totals based on condition in another row

Question

Really struggling to understand the best way of doing this...

I have a table of data

**StudentID  AssessmentCode  ResultGroup    Result**
  46933          12ENG        IBLevel          HL
  46933          12ENG        Mark             6
  46933          12ECO        IBLevel          HL
  46933          12ECO        Mark             5
  46933          12GEO        IBLevel          SL
  46933          12GEO        Mark             6
  46933          12LAN        IBLevel          HL
  46933          12LAN        Mark             4
  46933          12PED        IBLevel          SL
  46933          12PED        Mark             5
  46933          12SCI        IBLevel          SL
  46933          12SCI        Mark             3
  67767          12FRE        IBLevel          HL
  67767          12FRE        Mark             4
  67767          12MAT        IBLevel          SL
  67767          12MAT        Mark             5
  and so on...

Unfortunately the result column holds 2 different bits of info. The level a student does (HL = High Level; SL = Standard Level) and then the result for that subject at that level. Note that for each student, 2 rows are generated per subject a student does, one row has the IBLevel and the next row has the result for that level.

How can I rearrange the data to get something like

StudentID    HLResult    SLResult    TotalResult   CountofHL  CountofSL
  46933         15          14           29            3          3
  67767         13          10           23            4          2

So each student has one row of data with totals for HL, SL, both together and then a count of the number of HL and SL subjects. As I said, really not sure of the best way of going about this. In the end, I would also like to extend this to get some columns which, based on the results give some warning messages eg the TotalResult has to be great than 24 so I would love a column that simply returns whether this has been achieved or not...

StudentID  HLResult  SLResult  TotalResult CountofHL CountofSL MoreThan24
  46933       15        14         29          3        3         True
  67767       13        10         23          4        2         False

Any help would be greatly appreciated...

Answer 1

Your table seems to have some serious normalization issues. You have to perform an INNER JOIN on AssessmentCode first:

SELECT t1.StudentID, t1.AssessmentCode, 
       t1.Result AS Level, CAST(t2.Result AS INT) AS Mark 
FROM (
   SELECT StudentID, AssessmentCode, Result
   FROM mytable
   WHERE ResultGroup = 'IBLevel' ) AS t1
INNER JOIN (
   SELECT StudentID, AssessmentCode, Result
   FROM mytable
   WHERE ResultGroup = 'Mark' ) AS t2
ON t1.StudentID = t2.StudentID AND t1.AssessmentCode = t2.AssessmentCode

to get one row per AssessmentCode.

Output:

StudentID   AssessmentCode  Level   Mark
-----------------------------------------
46933       12ENG           HL      6
46933       12ECO           HL      5
46933       12GEO           SL      6
46933       12LAN           HL      4
46933       12PED           SL      5
46933       12SCI           SL      3
67767       12FRE           HL      4
67767       12MAT           SL      5

You can now wrap the above query in a CTE and perform conditional aggregation to get required result:

;WITH CTE AS (
 ... above query here
)
SELECT StudentID, 
       SUM(CASE WHEN Level = 'HL' THEN Mark ELSE 0 END) AS HLResult,
       SUM(CASE WHEN Level = 'SL' THEN Mark ELSE 0 END) AS SLResult,
       SUM(Mark) AS TotalResult,
       COUNT(CASE WHEN Level = 'HL' THEN 1 END) AS CountofHL,
       COUNT(CASE WHEN Level = 'SL' THEN 1 END) AS CountofSL,
       CASE WHEN SUM(Mark) > 24 THEN 'True'
            ELSE 'False'
       END AS MoreThan24
FROM CTE
GROUP BY StudentID  

Output:

StudentID   HLResult    SLResult    TotalResult CountofHL   CountofSL   MoreThan24
46933       15          14          29          3           3           True
67767       4           5           9           1           1           False

Demo here

Answer 2

Version1:

SELECT t1.StudentID, 
SUM(CASE WHEN t1.Result = 'HL' THEN t2.Result ELSE 0 END) HLResult,
SUM(CASE WHEN t1.Result = 'SL' THEN t2.Result ELSE 0 END) SLResult,
SUM(CAST(t2.Result AS INT)) TotalResult,
SUM(CASE WHEN t1.Result = 'HL' THEN 1 ELSE 0 END) CountofHL,
SUM(CASE WHEN t1.Result = 'SL' THEN 1 ELSE 0 END) CountofSL,
CASE WHEN SUM(CAST(t2.Result AS INT)) > 24 THEN 'True' ELSE 'False' END MoreThan24    
FROM @t t1
JOIN @t t2 ON t1.StudentID = t2.StudentID AND 
              t1.AssessmentCode = t2.AssessmentCode AND 
              t1.ResultGroup = 'IBLevel' AND 
              t2.ResultGroup = 'Mark'
GROUP BY t1.StudentID

Version2:

WITH cte1 AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY StudentID, AssessmentCode 
          ORDER BY CASE WHEN ResultGroup = 'IBLevel' THEN 1 ELSE 2 END) AS rn FROM @t),
cte2 AS(SELECT StudentID, 
               AssessmentCode, 
               SUM(CASE WHEN Result = 'HL' THEN 1 ELSE 0 END) HL, 
               SUM(CASE WHEN Result = 'SL' THEN 1 ELSE 0 END) SL, 
               MAX(CASE WHEN rn = 2 THEN Result END) R
        FROM cte1
        GROUP BY StudentID, AssessmentCode)
SELECT StudentID, 
       SUM(hl*R) HLResult, 
       SUM(sl*R) SLResult, 
       SUM((hl + sl)*r) TotalResult, 
       SUM(hl) CountofHL, 
       SUM(sl) CountofSL,
       CASE WHEN SUM((hl + sl)*r) > 24 THEN 'True' ELSE 'False' END MoreThan24    
FROM cte2
GROUP BY StudentID

Output:

StudentID   HLResult    SLResult    TotalResult CountofHL   CountofSL   MoreThan24
46933       15          14          29          3           3           True
67767       4           5           9           1           1           False

Just replace @t with your table.

Answer 3

My Version of Your Table

DECLARE @yourTable TABLE
(
    StudentID       INT,
    AssessmentCode  CHAR(5),
    ResultGroup     VARCHAR(10),
    Result          VARCHAR(5)
)

INSERT INTO @yourTable
VALUES  (46933,'12ENG','IBLevel','HL'),
        (46933,'12ENG','Mark','6'),
        (46933,'12ECO','IBLevel','HL'),
        (46933,'12ECO','Mark','5'),
        (46933,'12GEO','IBLevel','SL'),
        (46933,'12GEO','Mark','6'),
        (46933,'12LAN','IBLevel','HL'),
        (46933,'12LAN','Mark','4'),
        (46933,'12PED','IBLevel','SL'),
        (46933,'12PED','Mark','5'),
        (46933,'12SCI','IBLevel','SL'),
        (46933,'12SCI','Mark','3'),
        (67767,'12FRE','IBLevel','HL'),
        (67767,'12FRE','Mark','4'),
        (67767,'12MAT','IBLevel','SL'),
        (67767,'12MAT','Mark','5');

Actual Query

WITH CTE_ResultValue
AS
(
    SELECT  StudentID,
            AssessmentCode,
            ResultGroup,
            CAST(Result AS INT) AS ResultValue
    FROM @yourTable
    WHERE ISNUMERIC(Result) = 1
),
CTE_IBLevel
AS
(
    SELECT  StudentID,
        AssessmentCode,
        Result AS IBLevel
    FROM @yourTable
    WHERE ISNUMERIC(Result) = 0
),
CTE_Normalized
AS
(
    SELECT  A.StudentID,
            A.AssessmentCode,
            A.ResultGroup,
            A.ResultValue,
            B.IBLevel
    FROM CTE_ResultValue    AS A
    INNER JOIN CTE_IBLevel  AS B
    ON      A.StudentID = B.StudentID
        AND A.AssessmentCode = B.AssessmentCode
)

SELECT  [StudentID],
        [HLResult]      = SUM(CASE WHEN IBLevel = 'HL' THEN ResultValue END),
        [SLResult]      = SUM(CASE WHEN IBLevel = 'SL' THEN ResultValue END),
        [TotalResult]   = SUM(ResultValue),
        [CountOfHL]     = SUM(CASE WHEN IBLevel = 'HL' THEN 1 END),
        [CountOfSL]     = SUM(CASE WHEN IBLevel = 'SL' THEN 1 END)
FROM CTE_Normalized
GROUP BY StudentID

Normalization

Your table is in serious need of normalization. If you can change it, bare minimal change would look like this:

CREATE TABLE dbo.NormalizedTable
(
    StudentID       INT,
    AssessmentCode  CHAR(5),
    ResultGroup     VARCHAR(25),
    ResultValue     SMALLINT, --smallint range should be plenty. It can store values from -32,768 to 32,767
    IBLevel         CHAR(2)
)
INSERT INTO dbo.NormalizedTable
SELECT *
FROM CTE_Normalized

If you can't change the way your data is structured, I'd recommend creating a view from my CTE_normalized

CREATE VIEW vw_normalizedTable
AS
WITH CTE_ResultValue
AS
(
    SELECT  StudentID,
            AssessmentCode,
            ResultGroup,
            CAST(Result AS INT) AS ResultValue
    FROM @yourTable
    WHERE ISNUMERIC(Result) = 1
),
CTE_IBLevel
AS
(
    SELECT  StudentID,
        AssessmentCode,
        Result AS IBLevel
    FROM @yourTable
    WHERE ISNUMERIC(Result) = 0
),

SELECT  A.StudentID,
        A.AssessmentCode,
        A.ResultGroup,
        A.ResultValue,
        B.IBLevel
FROM CTE_ResultValue    AS A
INNER JOIN CTE_IBLevel  AS B
ON      A.StudentID = B.StudentID
    AND A.AssessmentCode = B.AssessmentCode
GO