Calculate SubTotal and Grand Total in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

I have following TableData

tblStudents

AdmissionNo

SName

FName

R-1

ABC

SKY

R-2

XYZ

ABC

R-3

SKY

XYZ

  tblExam

SetExamID

ExamName

14

Nov

15

Dec

tblSetMarks

AdmissionNo

SubjectID

SetExamID

ObtainMarks

R-1

42

14

23

R-2

42

14

23

R-3

42

14

23

R-1

43

15

24

R-2

43

15

24

R-3

43

15

24

declare @StdID varchar(50)
Set @StdID= 'R-000003'
select * into #TempMarkss
from (
select *
from ( select AdmissionNo,[SubjectID],[SetExamID],[Marks]
from tblSetMarks
where AdmissionNo= @StdID)
as tbl
pivot (sum(Marks) for [SetExamID] in ([14],[15])
) as PVT
) as s
select CONVERT(VARCHAR(20),SubjectID) SubjectID,[14],[15],[14]+[15] as 'G Total' from #TempMarkss
union all
select 'Total', sum([14]), sum ([15]), sum ([14]+[15]) from #TempMarkss
drop table #TempMarkss

This query is showing following correct output

Showing output 

SubjectID

14

15

G Total

42

23

24

47

43

23

24

47

Total

46

48

94

but i want to show data like this 

Required output

 

AdmissionNo

SName

SubjectID

Nov

Dec

G Total

R-01

ABC

42

23

24

47

R-02

ABC

43

23

24

47

 

 

Total

46

48

94

 

Posted 3 months ago Modified on 3 months ago

Hi smile,

Refer below query.

SQL

CREATE TABLE #tblStudents(AdmissionNo VARCHAR(10),SName VARCHAR(20), FName VARCHAR(20))
INSERT INTO #tblStudents VALUES('R-1','ABC','SKY')
INSERT INTO #tblStudents VALUES('R-2','XYZ','ABC')
INSERT INTO #tblStudents VALUES('R-3','SKY','XYZ')
 
CREATE TABLE #tblExam(SetExamID INT,ExamName VARCHAR(20))
INSERT INTO #tblExam VALUES(14,'Nov')
INSERT INTO #tblExam VALUES(15,'Dec')
 
CREATE TABLE #tblSetMarks(AdmissionNo VARCHAR(10),SubjectID INT, SetExamID INT,Marks INT)
INSERT INTO #tblSetMarks VALUES('R-1',42,14,23)
INSERT INTO #tblSetMarks VALUES('R-2',42,14,23)
INSERT INTO #tblSetMarks VALUES('R-3',42,14,23)
INSERT INTO #tblSetMarks VALUES('R-1',43,15,24)
INSERT INTO #tblSetMarks VALUES('R-2',43,15,24)
INSERT INTO #tblSetMarks VALUES('R-3',43,15,24)
 
declare @StdID varchar(50)
Set @StdID = 'R-3'
select * into #TempMarkss
from (
    select *
    from (  select s.AdmissionNo,s.SName,[SubjectID],e.SetExamID,[Marks],ExamName
            from #tblSetMarks m
            INNER JOIN #tblStudents s on s.AdmissionNo = m.AdmissionNo
            INNER JOIN #tblExam e ON e.SetExamID = m.SetExamID
            where s.AdmissionNo= @StdID
            ) as tbl
            pivot (SUM(Marks) for [ExamName] in([Nov],[Dec])) as PVT
) as s
select AdmissionNo
    ,SName
    ,CONVERT(VARCHAR(20),SubjectID) SubjectID
    ,ISNULL([Nov],0) Nov
    ,ISNULL([Dec],0) Dec
    ,ISNULL([Nov],0) + ISNULL([Dec],0) as 'G Total' 
from #TempMarkss
union all
select ''
    ,''
    ,'Total'
    ,sum([Nov])
    ,sum([Dec])
    ,sum(ISNULL([Nov],0)+ISNULL([Dec],0))
from #TempMarkss

drop table #tblStudents
drop table #tblExam
drop table #tblSetMarks
DROP TABLE #TempMarkss

Output

AdmissionNo SName SubjectID Nov Dec G Total
R-3 SKY 42 23 0 23
R-3 SKY 43 0 24 24
    Total 23 24 47