locked
group table by month and year in ASP.NET RRS feed

  • Question

  • User-1892044535 posted
    
    


    hi Guys i am trying to display my table by month and year my table works fine but i am trying to displayed by month and year

    like this image example  i draw here

    here

    i don't know how to do this here is my code 

    var query = (from t in db.pations
                             let Agerange = (
                                          t.Age >= 1 && t.Age < 5 ? "age from to 5" :
                                          t.Age >= 5 && t.Age < 15 ? "age from 5 to 15 ":
                                          t.Age >= 15 && t.Age < 25 ? "age from 15 to 25":
                                          t.Age >= 25 && t.Age < 45 ? "age from 25 to 45":
                                          "45+")
    let Sex = (
     t.Sex == 0  ?  "boys" : "girls")
                             group t by new { Agerange, Sex,date } into g
                             select new UserRange { AgeRange = g.Key.Agerange,date=g.key.mydate.month Gender = g.Key.Sex, Count = g.Count() }).ToList();
    
    var boysTotal = new UserRange() { Gender = "boys", AgeRange = "sum", Count = query.Where(c => c.Gender == "boys").Sum(c => c.Count) };
    
    var grilsTotal = new UserRange() { Gender = "girls", AgeRange = "sum", Count = query.Where(c => c.Gender == "girls").Sum(c => c.Count) };
    
                query.Add(boysTotal);
    
                query.Add(grilsTotal);
                ViewBag.UserData = query;

    and the view of this table like this

    <table border="1"class="table table-responsive table-bordered table-hover table-striped "  >
    
        <tr>
            @foreach (var item in ViewBag.UserData)
            {
                <th>@item.AgeRange </th>
            }
        </tr>
        <tr>
            @foreach (var item in ViewBag.UserData)
            {
                <th>@item.Gender </th>
            }
        </tr>
        <tr>
            @foreach (var item in ViewBag.UserData)
            {
                <td>@item.Count </td>
            }
        </tr>
    </table>



    i need to group this by month and year like the image and i really thank you supporting me with help that mean a lot to me
    Monday, December 17, 2018 7:12 AM

Answers

  • User-893317190 posted

    Hi mas mas,

    You could try the code below.

     public ActionResult ShowPation()
            {
                var query1 = from t in db.pations
                          
                            let Agerange = (
    
                                         t.age >= 1 && t.age < 5 ? "age from to 5" :
                                         t.age >= 5 && t.age < 15 ? "age from 5 to 15 " :
                                         t.age >= 15 && t.age < 25 ? "age from 15 to 25" :
                                         t.age >= 25 && t.age < 45 ? "age from 25 to 45" :
                                         "45+")
                               
                            group new { t.age,t.sex,Agerange} by new { t.date.Year, t.date.Month,Agerange } into g
                          
                            select g;
       
    
                var query2 = from g in query1 
                           
                            select new { mycount = g.Count(),g.Key.Year,g.Key.Month,g.Key.Agerange };  //select count of every group
    
    
                var query3 = from i in query2                               //group every agerange  according to the year and month
                             group i by new { i.Year, i.Month } into g
                             select g;
    
              
                Dictionary<string,List< UserRange>> dic = new Dictionary<string, List<UserRange>>();// fill the dictionary using the final data
                foreach (var item in query3)
                {
                  
                    foreach (var item1 in item)
                    {
                        if (!dic.ContainsKey(item.Key.Month + "/" + item.Key.Year))
                        {
    
                            dic[item.Key.Month + "/" + item.Key.Year] = new List<UserRange>();
                          
                        }
                        dic[item.Key.Month + "/" + item.Key.Year].Add(new UserRange { Count = item1.mycount, Agerange = item1.Agerange });
                       
                    }
                    dic[item.Key.Month + "/" + item.Key.Year] = dic[item.Key.Month + "/" + item.Key.Year].OrderByDescending(i => i.Agerange).ToList();//sort the data according to Agerange
                }
             
                return View(dic);
    
    
    
            }
           

    My model.

     [Table("pation")]
        public partial class pation
        {
            public int id { get; set; }
    
            public int? age { get; set; }
    
            [StringLength(50)]
            public string sex { get; set; }
    
            public DateTime date { get; set; }
        }


     public class UserRange
        {
            public string Agerange { get; set; }
            public int Count { get; set; }
    
        }

    The result. Because I don't have age from 25 - 45 , there are only three columns.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 18, 2018 6:32 AM
  • User-893317190 posted

    Hi mas mas,

    I'm sorry, I forget to show you how I write my view.

    Below is my code in cshtml.

    @{
        ViewBag.Title = "ShowPation";
    }
    
    @model  Dictionary <string,List <MVCLearning.Controllers.UserRange>>
    <h2>ShowPation</h2>
    
    
    
    <table border="1" class="table table-responsive table-bordered table-hover table-striped ">
    
        @foreach (string key in Model.Keys) // loop through the dict's keys to get all the date string
        {
    
            <tr><td colspan="3">@key</td></tr>  // show the date string
            <tr>
                @foreach (MVCLearning.Controllers.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 (MVCLearning.Controllers.UserRange item1 in Model[key])
                {
                    <td>@item1.Count</td> //show the Count in td
                }
            </tr>
    
        }
    
        
    </table>
    
    

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 19, 2018 1:07 AM

All replies

  • User-893317190 posted

    Hi mas mas,

    You could try the code below.

     public ActionResult ShowPation()
            {
                var query1 = from t in db.pations
                          
                            let Agerange = (
    
                                         t.age >= 1 && t.age < 5 ? "age from to 5" :
                                         t.age >= 5 && t.age < 15 ? "age from 5 to 15 " :
                                         t.age >= 15 && t.age < 25 ? "age from 15 to 25" :
                                         t.age >= 25 && t.age < 45 ? "age from 25 to 45" :
                                         "45+")
                               
                            group new { t.age,t.sex,Agerange} by new { t.date.Year, t.date.Month,Agerange } into g
                          
                            select g;
       
    
                var query2 = from g in query1 
                           
                            select new { mycount = g.Count(),g.Key.Year,g.Key.Month,g.Key.Agerange };  //select count of every group
    
    
                var query3 = from i in query2                               //group every agerange  according to the year and month
                             group i by new { i.Year, i.Month } into g
                             select g;
    
              
                Dictionary<string,List< UserRange>> dic = new Dictionary<string, List<UserRange>>();// fill the dictionary using the final data
                foreach (var item in query3)
                {
                  
                    foreach (var item1 in item)
                    {
                        if (!dic.ContainsKey(item.Key.Month + "/" + item.Key.Year))
                        {
    
                            dic[item.Key.Month + "/" + item.Key.Year] = new List<UserRange>();
                          
                        }
                        dic[item.Key.Month + "/" + item.Key.Year].Add(new UserRange { Count = item1.mycount, Agerange = item1.Agerange });
                       
                    }
                    dic[item.Key.Month + "/" + item.Key.Year] = dic[item.Key.Month + "/" + item.Key.Year].OrderByDescending(i => i.Agerange).ToList();//sort the data according to Agerange
                }
             
                return View(dic);
    
    
    
            }
           

    My model.

     [Table("pation")]
        public partial class pation
        {
            public int id { get; set; }
    
            public int? age { get; set; }
    
            [StringLength(50)]
            public string sex { get; set; }
    
            public DateTime date { get; set; }
        }


     public class UserRange
        {
            public string Agerange { get; set; }
            public int Count { get; set; }
    
        }

    The result. Because I don't have age from 25 - 45 , there are only three columns.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 18, 2018 6:32 AM
  • User-1892044535 posted

    hi mr.Ackerly Xu

    that's what i am looking for the hole 2 weeks  boss yeah but how did you bass this in the view how is view looks like please man ?  

    Tuesday, December 18, 2018 2:03 PM
  • User-893317190 posted

    Hi mas mas,

    I'm sorry, I forget to show you how I write my view.

    Below is my code in cshtml.

    @{
        ViewBag.Title = "ShowPation";
    }
    
    @model  Dictionary <string,List <MVCLearning.Controllers.UserRange>>
    <h2>ShowPation</h2>
    
    
    
    <table border="1" class="table table-responsive table-bordered table-hover table-striped ">
    
        @foreach (string key in Model.Keys) // loop through the dict's keys to get all the date string
        {
    
            <tr><td colspan="3">@key</td></tr>  // show the date string
            <tr>
                @foreach (MVCLearning.Controllers.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 (MVCLearning.Controllers.UserRange item1 in Model[key])
                {
                    <td>@item1.Count</td> //show the Count in td
                }
            </tr>
    
        }
    
        
    </table>
    
    

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 19, 2018 1:07 AM
  • User-1892044535 posted

    hi  mr.Ackerly Xu

    bro thank you so much man it work like charm laughing thank you brother i really thank  you man and i am sorry for that i make you do it for me but you helped me man thank you bro

    Thursday, December 20, 2018 7:34 PM
  • User-1892044535 posted

    hi mr 

    how you doing  i have problem  i tried to add columns for count  boys and girls foreach rang of ages and it work  so will but i try to sum all boys and girls but it gives me error

    i don't know how to do this can you help  me look what i tries 

            public ActionResult ShowPation()
            {
                var query1 = from t in db.Pations
    
    
                             let Agerange = (
                                         t.Age >= 0 && t.Age < 1 ? "Age Under 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 44" :
                                         "over 45"
                                         )
                             let Sex = (
                            t.Sex == 0 ? "boys" : "girls"
    
                            )
                             group new { t.Age, t.Sex, Agerange } by new { t.DateWared.Year, t.DateWared.Month, Agerange,Sex } 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 };  //select count of every group
    
    
                var query3 = from i in query2                               //group every agerange  according to the year and month
                             group i by new { i.Year, i.Month } into g
                             select g;
    
    
                Dictionary<string, List<UserRange>> dic = new Dictionary<string, List<UserRange>>();// fill the dictionary using the final data
                foreach (var item in query3)
                {
    
                    foreach (var item1 in item)
                    {
                        if (!dic.ContainsKey(item.Key.Month + "/" + item.Key.Year))
                        {
    
                            dic[item.Key.Month + "/" + item.Key.Year] = new List<UserRange>();
    
                        }
                        dic[item.Key.Month + "/" + item.Key.Year].Add(new UserRange { Count = item1.mycount, AgeRange = item1.Agerange,boysTotal=item1.Sex,grilsTotal=item1.Sex});
    
                        var boysTotal = new UserRange() { Gender = "boys", AgeRange = "sum boys", Count = dic.Where(c => c.Gender == "boys").Sum(c => c.Count) };
    
                        var grilsTotal = new UserRange() { Gender = "girls", AgeRange = "sum girls", Count = dic.Where(c => c.Gender == "girls").Sum(c => c.Count) };
    
                        dic.Add(boysTotal);
    
                        dic.Add(grilsTotal);
                        Total = item1.Sum(a => a.boysTotal) + item1.Sum(acs => a.grilsTotal)
                    }
                    dic[item.Key.Month + "/" + item.Key.Year] = dic[item.Key.Month + "/" + item.Key.Year].OrderByDescending(i => i.AgeRange).ToList();//sort the data according to Agerange
                }
    
                return View(dic);

    i don't know what to do 

    here

    Friday, December 21, 2018 5:16 PM
  • User-893317190 posted

    Hi mas mas,

    If you want to sum girls and boys , you should sum up the boys and girls count in second query.

    Below is my code.

     public ActionResult ShowPation()
            {
                var query1 = from t in db.pations
                          
                            let Agerange = (
    
                                         t.age >= 1 && t.age < 5 ? "age from to 5" :
                                         t.age >= 5 && t.age < 15 ? "age from 5 to 15 " :
                                         t.age >= 15 && t.age < 25 ? "age from 15 to 25" :
                                         t.age >= 25 && t.age < 45 ? "age from 25 to 45" :
                                         "45+")
                               
                            group new { t.age,t.sex,Agerange} by new { t.date.Year, t.date.Month,Agerange } into g
                          
                            select g;
       
    
                var query2 = from g in query1.OrderBy(i => i.Key.Year).ThenBy(i => i.Key.Month).ThenByDescending(i => i.Key.Agerange) //sort the data let it in the order of Agerange
                                    // sum up boys and girls of every small group
                            select new { boyCount = g.Where(item => item.sex=="boys").Count(), girlCount = g.Where(item => item.sex == "girls").Count() ,g.Key.Year,g.Key.Month,g.Key.Agerange };  //select count of every group
             
              
                var query3 = from i in query2                               //group every agerange in the according to the year and month
                             group i by new { i.Year, i.Month } into g
                             select g;
    
              
                Dictionary<string,List< UserRange>> dic = new Dictionary<string, List<UserRange>>();// fill the dictionary using the final data
                foreach (var item in query3)
                {
                  
                    foreach (var item1 in item)
                    {
                        if (!dic.ContainsKey(item.Key.Month + "/" + item.Key.Year))
                        {
    
                            dic[item.Key.Month + "/" + item.Key.Year] = new List<UserRange>();
                          
                        }                                                              //add boys's and girls's count to the viewModel
                        dic[item.Key.Month + "/" + item.Key.Year].Add(new UserRange { BoyCount = item1.boyCount, GirlCount = item1.girlCount, Agerange = item1.Agerange });
                       
                    }
                    dic[item.Key.Month + "/" + item.Key.Year] = dic[item.Key.Month + "/" + item.Key.Year].OrderByDescending(i => i.Agerange).ToList();
                }
             
                return View(dic);
    
    
    
            }
           
    
          
        }
        public class UserRange
        {
    // modify the view model to count boys and girls public string Agerange { get; set; } public int BoyCount { get; set; } public int GirlCount { get; set; } }

    My view.

    @model  Dictionary <string,List <MVCLearning.Controllers.UserRange>>
    <h2>ShowPation</h2>
    
    
    
    <table border="1" class="table table-responsive table-bordered table-hover table-striped ">
    
        
        @foreach (string key in Model.Keys)
        {
        
            <tr><td colspan="3">@key</td></tr>
            <tr>
                @foreach (MVCLearning.Controllers.UserRange item1 in Model[key])
                {
                    <td>@item1.Agerange</td>
                }
              
            </tr>
            <tr>
                @foreach (MVCLearning.Controllers.UserRange item1 in Model[key])
                { 
    show girls and boys <td>boys:@item1.BoyCount &nbsp;&nbsp;|&nbsp;&nbsp; girls:@item1.GirlCount</td> } </tr> } </table>

    The result.

    Best regards,

    Ackerly Xu

    Tuesday, January 1, 2019 6:23 AM