Jan 31, 2018

ASP.NET MVC: Display Total and Avg in Table footer in Razor Views

There are various cases in which you may want to display results from aggregate functions (Sum, Avg, Count, Min, Max) performed over the columns in the grid in their footer. This post explains how to display aggregate functions in ASP.NET MVC Razor views.

Used Environment for this post: VS 2015 Update 3, ASP.NET MVC 5

For ASP.NET Web Forms, Read following post:

Displaying Total in ASP.NET Gridview Footer Row Without using Template Field

With Generic List

Model:

Consider following model class:


public class ClassInfo
    {
       public String ClassName;
       public int Students;
    }

Controller:


  public class HomeController : Controller
    {
        public ActionResult Index()
        {
            List<ClassInfo> data = new List<ClassInfo>();
            Random rnd = new Random();
            for (int i = 1; i < 8; i++)
            {
               data.Add(new ClassInfo() {ClassName= "Class-" + i.ToString(), Students= rnd.Next(10, 50) });
            }
            return View(data);
        }
}

For data, we are generating random numbers between 10 and 50 and using index for class name.

View:

In Views > Home > Index

define model type


@model List<ClassInfo>

Here is HTML Table structure:


  <table class="table table-bordered table-responsive table-hover">
        <thead>
            <tr>
                <th>Class </th>
                <th>Students</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var d in Model)
            {
                <tr>
                    <td>@d.ClassName</td>
                    <td>@d.Students</td>
                </tr>
            }
        </tbody>
        <tfoot>
            <tr>
                <td>Total</td>
                <td>@Model.Sum(i => i.Students)</td>
            </tr>
            <tr>
                <td>Count</td>
                <td>@Model.Count()</td>
            </tr>
            <tr>
                <td>Avg</td>
                <td>@Model.Average(i => i.Students).ToString("0.00")</td>
            </tr>
            <tr>
                <td>Min</td>
                <td>@Model.Min(i => i.Students)</td>
            </tr>
            <tr>
                <td>Max</td>
                <td>@Model.Max(i => i.Students)</td>
            </tr>
        </tfoot>
</table>  

Here we have to calculate sum of Students column so @Model.Sum(i => i.Students) is used. Similarly, Avg, Min, Max are calculated.

sum total table footer

If you want to perform custom or conditional logic on controller side and want to show on footer then you can use ViewBag properties. Suppose you have to show count of classes which has more than average students:

In Index action of controller:


 	var avg = data.Average(x => x.Students);
        ViewBag.NoOfClassesMoreThanAvg = data.Count(x => x.Students > avg); 

On View side:


  	<tr>
                <td>Number of Classes More than Avg Students</td>
                <td>@ViewBag.NoOfClassesMoreThanAvg</td>
            </tr>

With DataTable

Controller:


 public class HomeController : Controller
    {
        public ActionResult Index()
        {
            
            Random rnd = new Random();         
            DataTable myDataTable = new DataTable();
            myDataTable.Columns.AddRange(new DataColumn[2] { new DataColumn("ClassName"), new DataColumn("Students", typeof(int)) });
            for (int i = 1; i < 8; i++)
            {
                myDataTable.Rows.Add("Class-" + i.ToString(), rnd.Next(10, 50));               
            }

            //Custom operation
            var data = myDataTable.AsEnumerable();
            var avg = data.Average(x => x.Field<int>("Students"));
            ViewBag.NoOfClassesMoreThanAvg = data.Count(x => x.Field<int>("Students") > avg);

            return View(myDataTable);
        }

View:



@model System.Data.DataTable
@using System.Data;
@{ var data = Model.AsEnumerable(); } 


<table class="table table-bordered table-responsive table-hover">
        <thead>
            <tr>
                <th>Class </th>
                <th>Students</th>
            </tr>
        </thead>
        <tbody>
            @foreach (DataRow row in Model.Rows)
            {
                <tr>
                    <td>@row["ClassName"]</td>
                    <td>@row["Students"]</td>
                </tr>
            }
        </tbody>
        <tfoot>
            <tr>
                <td>Total</td>
                <td>@data.Sum(i => i.Field<int>("Students"))</td>
            </tr>
            <tr>
                <td>Count</td>
                <td>@data.Count()</td>
            </tr>
            <tr>
                <td>Avg</td>
                <td>@data.Average(i => i.Field<int>("Students")).ToString("0.00")</td>
            </tr>
            <tr>
                <td>Min</td>
                <td>@data.Min(i => i.Field<int>("Students"))</td>
            </tr>
            <tr>
                <td>Max</td>
                <td>@data.Max(i => i.Field<int>("Students"))</td>
            </tr>
            <tr>
                <td>Number of Classes More than Avg Students</td>
                <td>@ViewBag.NoOfClassesMoreThanAvg</td>
            </tr>
        </tfoot>
</table>  

In this post, we saw how to display Sum(Total), Min, Max, Count and Average in table footer in ASP.NET MVC Razor views using Generic List and DataTable as data-source. Also, saw how to do custom operation on controller side and display it on View side.

Enjoy ASP.NET MVC !