SQL Query Roll-up Rows That Are Inside Rows

Good Day Experts!

I have been tasked with another assignment that I need some help with.  Hopefully you can point me in the right direction.  

I have a list of students that each have 4 classes.  So far we have made it through 3 quarters.  However, the student could have moved here at the start of the second quarter so would only have 2 quarters in the books.  When the class changes, I have to avg the grade percentage for as many quarters as the student was enrolled.  All of the class grade percentages need to be on one line per student.  I have attached a file to try and make it clearer for what I am trying to achieve.  

What is the best way to approach this?

Thank for the help,
jimbo99999
EE.xlsx
Jimbo99999Asked:
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.

ste5anSenior DeveloperCommented:
Step 1: The averages are a simple GROUP BY query in T-SQL.
Step 2: Depends on your requirements. As you post is tagged VisualBasic.NET, I would do the pivoting in the frond-end.
0
Jimbo99999Author Commented:
Hello...thank you for your response.  I have to do this in a SQL Query/Stored Procedure.  

I apologize for the confusion on the VisualBasic.Net category. I was just trying to perhaps get some input from a wider range of experts.

Thanks,
Ed
0
ste5anSenior DeveloperCommented:
Here you need  the PIVOT clause then for step 2. But you should explain your use-case/data model first. E.g. something like

DECLARE @Sample TABLE
    (
        Student INT ,
        Class VARCHAR(255) ,
        Term CHAR(2) ,
        GradePercent NUMERIC(5, 2)
    );

INSERT INTO @Sample ( Student ,
                      Class ,
                      Term ,
                      GradePercent )
VALUES ( 2222, 'Math', 'Q1', 89.4 ) ,
       ( 2222, 'Math', 'Q2', 78.9 ) ,
       ( 2222, 'Math', 'Q3', 91.2 ) ,
       ( 2222, 'English', 'Q1', 87.55 ) ,
       ( 2222, 'English', 'Q2', 67.7 ) ,
       ( 2222, 'English', 'Q3', 79.3 ) ,
       ( 2222, 'Science', 'Q2', 95.9 ) ,
       ( 2222, 'Science', 'Q3', 94.5 ) ,
       ( 2222, 'SocStudies', 'Q1', 66.57 ) ,
       ( 2222, 'SocStudies', 'Q2', 69.38 ) ,
       ( 2222, 'SocStudies', 'Q3', 77.25 );

SELECT   S.Student ,
         S.Class ,
         AVG(S.GradePercent)
FROM     @Sample S
GROUP BY S.Student ,
         S.Class;

WITH Averages
AS ( SELECT   S.Student ,
              S.Class ,
              AVG(S.GradePercent) AS Average
     FROM     @Sample S
     GROUP BY S.Student ,
              S.Class )
SELECT *
FROM   Averages A
    PIVOT (   SUM(Average)
              FOR Class IN ( English, Math, Science, SocStudies )) P;

Open in new window

when Class is problem invariant. Otherwise you need dynamic SQL, but this can raise some scope problems. E.g.

USE tempdb;

CREATE TABLE [Sample]
    (
        Student INT ,
        Class VARCHAR(255) ,
        Term CHAR(2) ,
        GradePercent NUMERIC(5, 2)
    );

INSERT INTO [Sample] ( Student ,
                       Class ,
                       Term ,
                       GradePercent )
VALUES ( 2222, 'Math', 'Q1', 89.4 ) ,
       ( 2222, 'Math', 'Q2', 78.9 ) ,
       ( 2222, 'Math', 'Q3', 91.2 ) ,
       ( 2222, 'English', 'Q1', 87.55 ) ,
       ( 2222, 'English', 'Q2', 67.7 ) ,
       ( 2222, 'English', 'Q3', 79.3 ) ,
       ( 2222, 'Science', 'Q2', 95.9 ) ,
       ( 2222, 'Science', 'Q3', 94.5 ) ,
       ( 2222, 'SocStudies', 'Q1', 66.57 ) ,
       ( 2222, 'SocStudies', 'Q2', 69.38 ) ,
       ( 2222, 'SocStudies', 'Q3', 77.25 ) ,
       ( 2222, 'Partey', 'Q3', 99.87 );
GO

DECLARE @Columns NVARCHAR(MAX);
DECLARE @Statement NVARCHAR(MAX) = N'
WITH Averages
AS ( SELECT   S.Student ,
              S.Class ,
              AVG(S.GradePercent) AS Average
     FROM     [Sample] S
     GROUP BY S.Student ,
              S.Class )
SELECT *
FROM   Averages A
    PIVOT (   SUM(Average)
              FOR Class IN ( @Columns )) P;
';

SET @Columns = STUFF((   SELECT   DISTINCT ', ' + QUOTENAME(S.Class)
                         FROM     [Sample] S
                         ORDER BY ', ' + QUOTENAME(S.Class)
                         FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,
                     1 ,
                     2 ,
                     '');
SET @Statement = REPLACE(@Statement, '@Columns', @Columns);
EXECUTE ( @Statement );
GO

DROP TABLE [Sample];
GO

Open in new window

0

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
Jimbo99999Author Commented:
Thank you for the direction on using Pivot.  I will study it now and implement into my structure.
0
Jimbo99999Author Commented:
Thank you for the help.  I was able to get it working!
0
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
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.