Display Group wise Rank in Windows Form DataGridView using C# and VB.Net

Last Reply 2 months ago By dharmendr

Posted 2 months ago

I have the following Data

SessionID TermID SetExamID ClassName SectionName SubjectID AdmissionNo Max Mark
8 6 18 Ten Yellow 123 R-000401 50 24
8 6 18 Ten Yellow 123 R-000402 50 23
8 6 18 Ten Yellow 123 R-000403 50 32
8 6 18 Ten Yellow 123 R-000404 50 23
8 6 18 Ten Yellow 123 R-000405 50 45
8 6 18 Ten Yellow 123 R-000406 50 33
8 6 18 Ten Yellow 123 R-000407 50 32
                        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
                            GROUP BY AdmissionNo ) t                   
                        order by AdmissionNo asc;

it is showing me result as like that

https://imgur.com/S80rB9j

i have total 20 students in a class ten and section yellow and 21 students in a class ten and section orange.

i want to display the record section wise hence i filter the record of Class Ten and Section Yellow.

but Y here i am seeing wrong RANK which is greater than 20. as there are 20 student in this section then RANK must not exceede the greater than 20.

how to fix it?

Posted 2 months ago Modified on 2 months ago

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