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.Query to calculate Percentage Grade in SQL Server<p>Hi <a class="username" rel="smile">smile</a>,</p> <p>Refer below sample query.</p> <p><span style="text-decoration: underline;"><strong>SQL</strong></span></p> <pre class="brush: sql">DECLARE @Test AS TABLE(Session VARCHAR(20), Term VARCHAR(10), Exam VARCHAR(10), AdmissionNo VARCHAR(10), Subject VARCHAR(10), Max INT, Mark INT) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-1&#39;,&#39;A1-T1&#39;,&#39;R-01&#39;,&#39;English&#39;,40,23) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-1&#39;,&#39;A2-T1&#39;,&#39;R-01&#39;,&#39;English&#39;,60,48) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-1&#39;,&#39;A1-T1&#39;,&#39;R-02&#39;,&#39;English&#39;,40,33) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-1&#39;,&#39;A2-T1&#39;,&#39;R-02&#39;,&#39;English&#39;,60,58) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-2&#39;,&#39;A1-T2&#39;,&#39;R-01&#39;,&#39;English&#39;,40,33) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-2&#39;,&#39;A2-T2&#39;,&#39;R-01&#39;,&#39;English&#39;,60,53) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-2&#39;,&#39;A1-T2&#39;,&#39;R-02&#39;,&#39;English&#39;,40,35) INSERT INTO @Test VALUES(&#39;S-19&#39;,&#39;Term-2&#39;,&#39;A2-T2&#39;,&#39;R-02&#39;,&#39;English&#39;,60,45) SELECT Session, &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Subject, &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; MIN(Obt)&#39;MinMark&#39;, &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; MAX(Obt) &#39;MaxMark&#39;, &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ROUND(CAST(MIN(Percentage) AS DECIMAL(10,2)),0) &#39;MinPercentage&#39;, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ROUND(CAST(MAX(Percentage) AS DECIMAL(10,2)),0) &#39;MaxPercentage&#39;, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (CASE WHEN&nbsp; Round((SUM(Obt)) * 100/ SUM(Total),1) &gt;= 90 THEN &#39;A+&#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN&nbsp; Round((SUM(Obt)) * 100/ SUM(Total),1) &gt;= 80 THEN &#39;A&#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN&nbsp; Round((SUM(Obt)) * 100/ SUM(Total),1) &gt;= 70 THEN &#39;B&#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN&nbsp; Round((SUM(Obt)) * 100/ SUM(Total),1) &gt;= 60 THEN &#39;C&#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN&nbsp; Round((SUM(Obt)) * 100/ SUM(Total),1) &gt;= 50 THEN &#39;D&#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN&nbsp; Round((SUM(Obt)) * 100/ SUM(Total),1) &gt;= 40 THEN &#39;E&#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE &#39;FAIL&#39; END ) AS Grade FROM( SELECT Session, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AdmissionNo, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Subject, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(Max) &#39;Total&#39;, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(Mark) &#39;Obt&#39;, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(Mark)*CAST(100 AS DECIMAL(10,2))/SUM(Max) &#39;Percentage&#39; FROM @Test WHERE Session=&#39;S-19&#39; GROUP BY Session,Subject,AdmissionNo) t GROUP BY Session,Subject</pre> <p><span style="text-decoration: underline;"><strong>Output</strong></span></p> <table border="1"> <tbody> <tr style="height: 15.0pt;"> <td style="height: 15.0pt; width: 48pt;" width="64" height="20"><strong>Session</strong></td> <td style="width: 48pt;" width="64"><strong>Subject</strong></td> <td style="width: 48pt;" width="64"><strong>MinMark</strong></td> <td style="width: 48pt;" width="64"><strong>MaxMark</strong></td> <td style="width: 48pt;" width="64"><strong>MinPercentage</strong></td> <td style="width: 48pt;" width="64"><strong>MaxPercentage</strong></td> <td style="width: 48pt;" width="64"><strong>Grade</strong></td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">S-19</td> <td>English</td> <td align="right">157</td> <td align="right">171</td> <td align="right">79</td> <td align="right">86</td> <td>A</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/965716/Query-to-calculate-Percentage-Grade-in-SQL-Server/https://www.aspforums.net:443/Threads/965716/Query-to-calculate-Percentage-Grade-in-SQL-Server/Mon, 13 May 2019 03:59:13 GMT