ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Calculate SubTotal and Grand Total in SQL Server<p>Hi&nbsp;<a class="username" rel="smile">smile,</a></p> <p>Refer below query.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">CREATE TABLE #tblStudents(AdmissionNo VARCHAR(10),SName VARCHAR(20), FName VARCHAR(20)) INSERT INTO #tblStudents VALUES(&#39;R-1&#39;,&#39;ABC&#39;,&#39;SKY&#39;) INSERT INTO #tblStudents VALUES(&#39;R-2&#39;,&#39;XYZ&#39;,&#39;ABC&#39;) INSERT INTO #tblStudents VALUES(&#39;R-3&#39;,&#39;SKY&#39;,&#39;XYZ&#39;) &nbsp; CREATE TABLE #tblExam(SetExamID INT,ExamName VARCHAR(20)) INSERT INTO #tblExam VALUES(14,&#39;Nov&#39;) INSERT INTO #tblExam VALUES(15,&#39;Dec&#39;) &nbsp; CREATE TABLE #tblSetMarks(AdmissionNo VARCHAR(10),SubjectID INT, SetExamID INT,Marks INT) INSERT INTO #tblSetMarks VALUES(&#39;R-1&#39;,42,14,23) INSERT INTO #tblSetMarks VALUES(&#39;R-2&#39;,42,14,23) INSERT INTO #tblSetMarks VALUES(&#39;R-3&#39;,42,14,23) INSERT INTO #tblSetMarks VALUES(&#39;R-1&#39;,43,15,24) INSERT INTO #tblSetMarks VALUES(&#39;R-2&#39;,43,15,24) INSERT INTO #tblSetMarks VALUES(&#39;R-3&#39;,43,15,24) &nbsp; declare @StdID varchar(50) Set @StdID = &#39;R-3&#39; select * into #TempMarkss from ( &nbsp;&nbsp;&nbsp; select * &nbsp;&nbsp;&nbsp; from (&nbsp; select s.AdmissionNo,s.SName,[SubjectID],e.SetExamID,[Marks],ExamName &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from #tblSetMarks m &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN #tblStudents s on s.AdmissionNo = m.AdmissionNo &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN #tblExam e ON e.SetExamID = m.SetExamID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where s.AdmissionNo= @StdID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) as tbl &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pivot (SUM(Marks) for [ExamName] in([Nov],[Dec])) as PVT ) as s select AdmissionNo &nbsp;&nbsp; &nbsp;,SName &nbsp;&nbsp; &nbsp;,CONVERT(VARCHAR(20),SubjectID) SubjectID &nbsp;&nbsp; &nbsp;,ISNULL([Nov],0) Nov &nbsp;&nbsp; &nbsp;,ISNULL([Dec],0) Dec &nbsp;&nbsp; &nbsp;,ISNULL([Nov],0) + ISNULL([Dec],0) as &#39;G Total&#39; from #TempMarkss union all select &#39;&#39; &nbsp;&nbsp; &nbsp;,&#39;&#39; &nbsp;&nbsp; &nbsp;,&#39;Total&#39; &nbsp;&nbsp; &nbsp;,sum([Nov]) &nbsp;&nbsp; &nbsp;,sum([Dec]) &nbsp;&nbsp; &nbsp;,sum(ISNULL([Nov],0)+ISNULL([Dec],0)) from #TempMarkss drop table #tblStudents drop table #tblExam drop table #tblSetMarks DROP TABLE #TempMarkss</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr> <td><strong>AdmissionNo</strong></td> <td><strong>SName</strong></td> <td><strong>SubjectID</strong></td> <td><strong>Nov</strong></td> <td><strong>Dec</strong></td> <td><strong>G Total</strong></td> </tr> <tr> <td>R-3</td> <td>SKY</td> <td>42</td> <td>23</td> <td>0</td> <td>23</td> </tr> <tr> <td>R-3</td> <td>SKY</td> <td>43</td> <td>0</td> <td>24</td> <td>24</td> </tr> <tr> <td>&nbsp;</td> <td>&nbsp;</td> <td>Total</td> <td>23</td> <td>24</td> <td>47</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/978910/Calculate-SubTotal-and-Grand-Total-in-SQL-Server/https://www.aspforums.net:443/Threads/978910/Calculate-SubTotal-and-Grand-Total-in-SQL-Server/Tue, 11 Dec 2018 03:28:17 GMT