none
linq joining 4 tables and returning a sum RRS feed

  • Question

  • hi,

       ive been using linq to sql for a program on enrollment record i work for department of education in the philippines..

    my question is is it possible to join 5 tables and group according to their school id and return a sum of the collumns.

    here is a description of the table

    id - pk

    school_id - int

    year_lvl - nvarchar

    female - int

    male - int

    all the tables are the same they just differ in the year level as in table 1 its only for 1st year students i did these because ive been having a problem returning sum i can only return a sum with parameters for the year level so i have to make 4 queries just to sum 1st year to fourth year for the same school that is why i decided to make 4 tables specific for each year lvl what i wanted to achieve is to join the tables with a table of the school name the output would return a sum of 1st year to fourth year together with their school name

    Wednesday, September 29, 2010 1:19 AM

All replies

  • hi

     

    here you can see the total number of male and female students from all tables grouped by school_id :

     

    //xaml:

     <Grid>

                <DataGrid Name="dg1" AutoGenerateColumns="True"/>

     </Grid>

     

     

    //cs:

      private void Window_Loaded(object sender, RoutedEventArgs e)

            {

                List<cust> lcust1 = new List<cust>();

                List<cust> lcust2 = new List<cust>();

                List<cust> lcust3 = new List<cust>();

                List<cust> lcust4 = new List<cust>();

                List<cust> lcust5 = new List<cust>();

                List<cust> lcust = new List<cust>();

                lcust1.Add(new cust() { Id = 1, SchoolId = 123, Year_lvl = "2006", Male = 15, Female = 20 });

                lcust1.Add(new cust() { Id = 2, SchoolId = 123, Year_lvl = "2006", Male = 27, Female = 30 });

                lcust1.Add(new cust() { Id = 3, SchoolId = 124, Year_lvl = "2006", Male = 30, Female = 30 });

     

                lcust2.Add(new cust() { Id = 1, SchoolId = 123, Year_lvl = "2007", Male = 16, Female = 21 });

                lcust2.Add(new cust() { Id = 2, SchoolId = 123, Year_lvl = "2007", Male = 25, Female = 30 });

                lcust2.Add(new cust() { Id = 3, SchoolId = 124, Year_lvl = "2007", Male = 34, Female = 35 });

     

                lcust3.Add(new cust() { Id = 1, SchoolId = 123, Year_lvl = "2008", Male = 10, Female = 22 });

                lcust3.Add(new cust() { Id = 2, SchoolId = 123, Year_lvl = "2008", Male = 20, Female = 33 });

                lcust3.Add(new cust() { Id = 3, SchoolId = 124, Year_lvl = "2008", Male = 30, Female = 39 });

     

                lcust4.Add(new cust() { Id = 1, SchoolId = 123, Year_lvl = "2009", Male = 18, Female = 20 });

                lcust4.Add(new cust() { Id = 2, SchoolId = 123, Year_lvl = "2009", Male = 27, Female = 33 });

                lcust4.Add(new cust() { Id = 3, SchoolId = 124, Year_lvl = "2009", Male = 33, Female = 35 });

     

                lcust5.Add(new cust() { Id = 1, SchoolId = 123, Year_lvl = "2010", Male = 19, Female = 24 });

                lcust5.Add(new cust() { Id = 2, SchoolId = 123, Year_lvl = "2010", Male = 27, Female = 30 });

                lcust5.Add(new cust() { Id = 3, SchoolId = 124, Year_lvl = "2010", Male = 32, Female = 30 });

     

                lcust.AddRange(lcust1);

                lcust.AddRange(lcust2);

                lcust.AddRange(lcust3);

                lcust.AddRange(lcust4);

                lcust.AddRange(lcust5);

     

                var total = from n in lcust

                            group n by n.SchoolId

                                into g

                                select new { g.Key, Males = lcust.Where(a => a.SchoolId == g.Key).Sum(a => a.Male), Females = lcust.Where(a => a.SchoolId == g.Key).Sum(a => a.Female) };

     

                dg1.ItemsSource = total.ToList();

            }

     

            private class cust

            {

                public int Id

                {

                    get { return id; }

                    set { id = value; }

                }

                public int SchoolId

                {

                    get { return schoolId; }

                    set { schoolId = value; }

                }

                public string Year_lvl

                {

                    get { return year_lvl; }

                    set { year_lvl = value; }

                }

                public int Male

                {

                    get { return male; }

                    set { male = value; }

                }

                public int Female

                {

                    get { return female; }

                    set { female = value; }

                }

                private int id;

                private int schoolId;

                private string year_lvl;

                private int male;

                private int female;

     

            }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. Regards, Alireza
    Wednesday, October 13, 2010 9:18 AM