How to display data rangewise?

Question

I want the data fetched from the database to appear as this

Total_marks | No of Students

10-20       | 2
20-30       | 1
30-40       | 3
so on

Sample data:

barcode(student id) | Total_marks                                                                                 
     200056 | 70
     200071 | 51
     200086 | 40
     200301 | 56
     200317 | 73
     200316 | 35
     200217 | 42
     200104 | 80
     200015 | 63

I tried :

     SELECT
count(*) as  no_of_students,
CASE 
    WHEN Total_marks BETWEEN 10 and 20 THEN '1'
    WHEN Total_marks BETWEEN 20 and 30 THEN '2'
    WHEN Total_marks BETWEEN 30 and 40 THEN '3'
    WHEN Total_marks BETWEEN 40 and 50 THEN '4'
    WHEN Total_marks BETWEEN 50 and 60 THEN '5'
    WHEN Total_marks BETWEEN 60 and 70 THEN '6'
    WHEN Total_marks BETWEEN 70 and 80 THEN '7'
    WHEN Total_marks BETWEEN 80 and 90 THEN '8'
    WHEN Total_marks BETWEEN 90 and 100 THEN '9'
END AS intervals
FROM
[database]
WHERE
0 - 10 = '0' and 10 - 20 = '1' and 20 - 30 = '2' and 30 - 40 = '3' and 40 -     50 = '4' and 50 - 60 = '5' and 60 - 70 = '6' and 70 -80 = '7' and 80 - 90 = '8' and 90 - 100 = '9' GROUP BY Total_marks

But i just get the column titles and no data. How to formulate the query correctly. Also I want this to be displayed as a chart in ASP.NET. where the code would be :

    {
    connection.Open();
            SqlCommand cmd = new SqlCommand("Query", connection);
            cmd.ExecuteNonQuery();


            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable ChartData = ds.Tables[0];

                //storing total rows count to loop on each Record  
            string[] XPointMember = new string[ChartData.Rows.Count];
            Chart1.ChartAreas[0].AxisX.IsStartedFromZero = true;

            decimal[] YPointMember = new decimal[ChartData.Rows.Count];

            int totalrows = ChartData.Rows.Count;

            if (totalrows > 0)
            {
                for (int count = 0; count < ChartData.Rows.Count; count++)
                {
                    //storing Values for X axis  
                    XPointMember[count] = ChartData.Rows[count]["Number_of_students"].ToString();

                    //storing values for Y Axis  
                    YPointMember[count] = Convert.ToDecimal(ChartData.Rows[count]["Total_accumulated_score_achieved" + "%"]);

                    connection.Close();
    }

Answer 1

You need to use CASE to get intervals and then GROUP BY same interval to get count of student which falls in the respective intervals.

Check this query and hope it helps you

Changed: Used table variable to store list of intervals and then used join to get the required output.

Declare @Intervals table (Interval varchar(10))
Insert into @Intervals values('10-20'),('21-30'),('31-40'),('41-50'),('51-60'),('61-70'),('71-80'),('81-90'),('91-100')

SELECT i.Interval as [Intervals], ISNULL(count(t.Intervals),0) as [NoOfStudents]
FROM (
  SELECT CASE  
    WHEN Total_marks BETWEEN 10 and 20 THEN '10-20'
    WHEN Total_marks BETWEEN 21 and 30 THEN '21-30'
    WHEN Total_marks BETWEEN 31 and 40 THEN '31-40'
    WHEN Total_marks BETWEEN 41 and 50 THEN '41-50'
    WHEN Total_marks BETWEEN 51 and 60 THEN '51-60'
    WHEN Total_marks BETWEEN 61 and 70 THEN '61-70'
    WHEN Total_marks BETWEEN 71 and 80 THEN '71-80'
    WHEN Total_marks BETWEEN 81 and 90 THEN '81-90'
    WHEN Total_marks BETWEEN 91 and 100 THEN '91-100'
    end as Intervals
  from @yourtable) t
  right join @Intervals i on i.Interval = t.Intervals 
group by i.Interval

Answer 2

Try This

create table StudentData
(
  StudentID INT,
  Total_Marks INT
)

insert into StudentData values(1,10)
insert into StudentData values(2,30)
insert into StudentData values(3,10)
insert into StudentData values(4,50)
insert into StudentData values(5,50)
insert into StudentData values(6,70)
insert into StudentData values(7,80)
insert into StudentData values(8,70)
insert into StudentData values(9,80)
insert into StudentData values(10,80)
insert into StudentData values(11,90)
insert into StudentData values(12,100)
insert into StudentData values(13,40)

SELECT t.Intervals as [Intervals], count(*) as [NoOfStudents]
FROM (
 SELECT CASE  
WHEN Total_marks BETWEEN 10 and 20 THEN '10-20'
WHEN Total_marks BETWEEN 20 and 30 THEN '20-30'
WHEN Total_marks BETWEEN 30 and 40 THEN '30-40'
WHEN Total_marks BETWEEN 40 and 50 THEN '40-50'
WHEN Total_marks BETWEEN 50 and 60 THEN '50-60'
WHEN Total_marks BETWEEN 60 and 70 THEN '60-70'
WHEN Total_marks BETWEEN 70 and 80 THEN '70-80'
WHEN Total_marks BETWEEN 80 and 90 THEN '80-90'
WHEN Total_marks BETWEEN 90 and 100 THEN '90-100'
end as Intervals
 from StudentData) t
 group by t.Intervals

 Output:

Intervals   NoOfStudents
  10-20       2
  20-30       1
  30-40       1
  40-50       2
  60-70       2
  70-80       3
  80-90       1
  90-100      1