Hi smile,
Check this example. Now please take its reference and correct your code.
C#
private void btnSearch_Click(object sender, EventArgs e)
{
string conString = "Server=.;DataBase=Test;UID=sa;PWD=123";
string query = @"SELECT *
FROM (
SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
ELSE 'FAIL' END ) AS Grade
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
ELSE 'FAIL' END ) AS Remarks
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
FROM tblSetMarks
WHERE SectionName = @Section AND ClassName = @Class
GROUP BY AdmissionNo ) t
order by AdmissionNo asc";
DataTable dt = new DataTable();
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query))
{
cmd.Parameters.AddWithValue("@Section", txtSectionName.Text);
cmd.Parameters.AddWithValue("@Class", txtClassName.Text);
using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
}
}
}
dataGridView1.DataSource = dt;
}
VB.Net
Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
Dim conString As String .;DataBase=Test;UID=sa;PWD=123"
Dim query As String = "SELECT * FROM (" _
+ "SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'" _
+ ",dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK" _
+ ",Round(sum(Mark) * 100/ sum(Max),1) as Percentage" _
+ ",(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'" _
+ "ELSE 'FAIL' END ) AS Grade" _
+ ",(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'" _
+ "ELSE 'FAIL' END ) AS Remarks" _
+ ",(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'" _
+ "WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'" _
+ "ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks" _
+ " FROM tblSetMarks" _
+ " WHERE SectionName = @Section AND ClassName = @Class" _
+ " GROUP BY AdmissionNo ) t" _
+ " order by AdmissionNo asc"
Dim dt As DataTable = New DataTable()
Using con As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(query)
cmd.Parameters.AddWithValue("@Section", txtSectionName.Text)
cmd.Parameters.AddWithValue("@Class", txtClassName.Text)
Using sda As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
End Using
End Using
End Using
dataGridView1.DataSource = dt
End Sub
Screenshot
