Calculate SubTotal and Grand Total in SQL Server

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Dec 07, 2018 08:21 AM
Forum: SQL Server
Answer: 1
Views: 5066

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

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 4

Reply
ANSWER
User: dharmendr
Replied: on Dec 11, 2018 03:28 AM
Modified: on Dec 11, 2018 06:52 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate SubTotal and Grand Total in SQL Server

Company

Explore

Follow us

Contact Us