Display Group By Sum in HTML Table using jQuery in ASP.Net MVC

masmas

SOLVED CLOSED ACTIVE ANSWERED
User: masmas
Posted: on Dec 31, 2018 09:58 AM
Forum: ASP.Net MVC
Answer: 1
Views: 7275
Sample Code: Download

hi guys

i am trying to sum all values from my table in 2 column how can i do this here example image for my question i want sum boys and sum girls

this is my table and i want to add this to column like this

what should i do to add this 2 column in my table

here is my controller

public ActionResult ShowPation()
{
    var query1 = from t in db.Pations.Include(b => b.Type_Services).ToList()
                 let Agerange =
    (
      t.Age >= 0 && t.Age < 1 ? "Age Under 1 year" :
                    t.Age >= 1 && t.Age < 5 ? "Age from 1 to 4" :
                    t.Age >= 5 && t.Age < 15 ? "Age from 5 to 14" :
                    t.Age >= 15 && t.Age < 25 ? "Age from 15 to 24" :
                    t.Age >= 25 && t.Age < 45 ? "Age from 25 to 46" :
                    "age over 47+"
     )
      let Sex = (t.Sex == 0 ? "boys" : "girls")
                 let Type_S = t.Type_Services.NameService
                 group new { t.Age, t.Sex, Agerange } by new { t.DateWared.Year, t.DateWared.Month, Agerange,Sex,t.Type_Services.NameService } into g
        select g;

    var query2 = from g in query1
        select new { mycount = g.Count(), g.Key.Year, g.Key.Month, g.Key.Agerange,g.Key.Sex ,g.Key.NameService,}; 

    var query3 = from i in query2                              
        group i by new { i.Year, i.Month ,i.NameService} into g
        select g;

    Dictionary<string, List<UserRange>> urn = new Dictionary<string, List<UserRange>>();
    foreach (var item in query3)
    {
      foreach (var item1 in item)
       {
            if (!urn.ContainsKey(item.Key.Month + "/" + item.Key.Year+ item.Key.NameService))
          {
                urn[item.Key.Month + "/" + item.Key.Year + item.Key.NameService] = new List<UserRange>();
          }
            urn[item.Key.Month + "/" + item.Key.Year+  item.Key.NameService].Add(new UserRange { Count = item1.mycount, AgeRange = item1.Agerange,Gender=item1.Sex});

       }
        urn[item.Key.Month + "/" + item.Key.Year+  item.Key.NameService] = urn[item.Key.Month + "/" + item.Key.Year+ item.Key.NameService].OrderByDescending(i => i.AgeRange).ToList();//sort the data according to Agerange
    }
    return View(urn);
}

<table border="1" class="table table-responsive table-bordered table-hover table-striped " style="height: 145px;text-align: center; border: solid 1px;border-radius: 13px;"> 
    @foreach (string key in Model.Keys) // loop through the dict's keys to get all the date string
    {
        <tr>
            <td colspan="12"><center>@key</center></td>
        </tr>  // show the date string
        <tr>
            @foreach (The_Hospital_Project.Models.UserRange item1 in Model[key]) //get the corresponding Agerange and Count of the date
            {
                <td>@item1.AgeRange</td>  // show the Agerange in td
            } 
        </tr>
        <tr>
            @foreach (The_Hospital_Project.Models.UserRange item1 in Model[key]) //get the corresponding Agerange and Count of the date
            {
                <td>@item1.Gender</td>  // show the Agerange in td
            } 
        </tr>
        <tr>
            @foreach (The_Hospital_Project.Models.UserRange item1 in Model[key])
            {
                <td>@item1.Count</td> //show the Count in td
            }
        </tr> 
    }
</table>

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Jan 01, 2019 12:18 AM
Modified: on Jan 02, 2019 12:20 AM
Report

Hi masmas,

For getting sum of boys and girls use jquery to calculate.

I have added two columns after foreach loop to display the Sum for Boys and Girls.

also assign a class for gender tr to loop through the td and calculate.

Check this example. Now please take its reference and correct your code.

Model

public class UserRange
{
    public int Count { get; set; }
    public string AgeRange { get; set; }
    public string Gender { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        Dictionary<string, List<UserRange>> urn = new Dictionary<string, List<UserRange>>();
        List<UserRange> ur = new List<UserRange>();
        ur.Add(new UserRange { Count = 2, AgeRange = "Age from 1 to 4", Gender = "girls" });
        ur.Add(new UserRange { Count = 2, AgeRange = "Age from 1 to 4", Gender = "boys" });
        ur.Add(new UserRange { Count = 2, AgeRange = "Age from 15 to 24", Gender = "girls" });
        ur.Add(new UserRange { Count = 4, AgeRange = "Age from 15 to 24", Gender = "boys" });
        ur.Add(new UserRange { Count = 1, AgeRange = "Age from 25 to 46", Gender = "boys" });
        ur.Add(new UserRange { Count = 4, AgeRange = "Age from 25 to 46", Gender = "girls" });
        ur.Add(new UserRange { Count = 1, AgeRange = "Age from 5 to 14", Gender = "girls" });
        ur.Add(new UserRange { Count = 2, AgeRange = "Age from 5 to 14", Gender = "boys" });
        ur.Add(new UserRange { Count = 1, AgeRange = "age over 47+", Gender = "boys" });
        ur.Add(new UserRange { Count = 1, AgeRange = "age over 47+", Gender = "girls" });
        ur.Add(new UserRange { Count = 2, AgeRange = "Age Under 1 year", Gender = "girls" });
        ur.Add(new UserRange { Count = 2, AgeRange = "Age Under 1 year", Gender = "boys" });
        urn.Add("12/2018 test3", ur);

        ur = new List<UserRange>();
        ur.Add(new UserRange { Count = 2, AgeRange = "Age from 15 to 24", Gender = "girls" });
        ur.Add(new UserRange { Count = 1, AgeRange = "Age from 25 to 46", Gender = "boys" });
        ur.Add(new UserRange { Count = 1, AgeRange = "Age from 25 to 46", Gender = "girls" });
        urn.Add("12/2018 test1", ur);

        ur = new List<UserRange>();
        ur.Add(new UserRange { Count = 10, AgeRange = "age over 47+", Gender = "girls" });
        ur.Add(new UserRange { Count = 12, AgeRange = "age over 47+", Gender = "boys" });
        ur.Add(new UserRange { Count = 15, AgeRange = "Age Under 1 year", Gender = "girls" });
        ur.Add(new UserRange { Count = 18, AgeRange = "Age Under 1 year", Gender = "boys" });
        urn.Add("12/2018 test2", ur);

        return View(urn);
    }
}

View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<IEnumerable<KeyValuePair<String,List<UserRange>>>>" %>

<%@ Import Namespace="_Sum_Rows.Models" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript">
        $(function () {
            CalculateBoysGirlsTotal();
        });

        function CalculateBoysGirlsTotal() {
            var trs = $('tr[class="gender"]');
            for (var rows = 0; rows < trs.length; rows++) {
                var boysTotal = 0;
                var girlsTotal = 0;
                var trGender = trs[rows];
                var trCount = $(trGender).next();
                for (var i = 0; i < $(trGender).find('td').length - 2; i++) {
                    if ($(trGender).find('td').eq(i).html().trim() == 'girls') {
                        boysTotal += parseInt(isNaN($(trCount).find('td').eq(i).html()) ? 0 : $(trCount).find('td').eq(i).html());
                    }
                    else if ($(trGender).find('td').eq(i).html().trim() == 'boys') {
                        girlsTotal += parseInt(isNaN($(trCount).find('td').eq(i).html()) ? 0 : $(trCount).find('td').eq(i).html());
                    }
                }
                $(trCount).find('td').eq($(trGender).find('td').length - 1).html(boysTotal);
                $(trCount).find('td').eq($(trGender).find('td').length - 2).html(girlsTotal);
            }
        }
    </script>
</head>
<body>
    <table border="1" class="table table-responsive table-bordered table-hover table-striped "
        style="height: 145px; text-align: center; border: solid 1px; border-radius: 13px;">
        <% foreach (var key in Model)
           { %>
        <tr><td colspan="14"><center><%: key.Key %></center></td></tr>
        <tr>
            <% foreach (var ageRange in key.Value)
               { %>
            <td><%: ageRange.AgeRange%></td>
            <% } %>
            <%--Added two colums for display sum--%>
            <td>Sum Boys</td>
            <td>Sum Girls</td>
        </tr>
        <tr class="gender">
            <% foreach (var gender in key.Value)
               { %>
            <td><%: gender.Gender%></td>
            <% } %>
            <%--Added two colums for display sum--%>
            <td>boys</td>
            <td>girls</td>
        </tr>
        <tr class="count">
            <% foreach (var count in key.Value)
               { %>
            <td><%: count.Count%></td>
            <% } %>
            <%--Added two colums for display sum--%>
            <td></td>
            <td></td>
        </tr>
        <% } %>
    </table>
</body>
</html>

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Display Group By Sum in HTML Table using jQuery in ASP.Net MVC

Company

Explore

Follow us

Contact Us