Linq group by multiple columns and sum | Select multiple fields group by and sum

In this article, we will learn to use LINQ to group by multiple properties and sum using C#. We have already learned How to use group by in SQL query.

I’m assuming that you are familiar with SQL queries and group by clause If not then please go through the following articles:

• How to use group by in SQL Server query?
• Understand Group by With Linq C#.

Select multiple fields group by and sum

I have a list of Customer objects, Now I want to group the list based on the City and ProductType, and return an object containing the City, ProductType, and the TotalOrderAmount.

If you want to do a query with LINQ in the list of customers and you really don’t know how to do LINQ to group by multiple properties and sum, then you have come to the right place. In this article, we will discuss how we can use the Linq group by multiple columns and sum them together in an object list.

Example 1:

Customers Collection

public class Customer
    {
        public List<Customer> Getcustomers()
        {
            List<Customer> customers = new List<Customer>();
            customers.Add(new Customer { Name = "John", City = "London", OrderAmount = 2000, ProductType = "Clothing" });
            customers.Add(new Customer { Name = "Mark", City = "Paris", OrderAmount = 1800, ProductType = "Jewelry" });
            customers.Add(new Customer { Name = "Josh", City = "Sydney", OrderAmount = 200, ProductType = "Household" });
            customers.Add(new Customer { Name = "Kath", City = "Paris", OrderAmount = 3500, ProductType = "Health" });
            customers.Add(new Customer { Name = "Karlin", City = "Sydney", OrderAmount = 2200, ProductType = "Household" });
            customers.Add(new Customer { Name = "Kovii", City = "London", OrderAmount = 3600, ProductType = "Jewelry" });
            customers.Add(new Customer { Name = "Jack", City = "Tokyo", OrderAmount = 1400, ProductType = "Clothing" });
            customers.Add(new Customer { Name = "Smith", City = "Paris", OrderAmount = 800, ProductType = "Health" });
            return customers;
        }
        public string Name { get; set; }
        public string City { get; set; }
        public double OrderAmount { get; set; }
        public string ProductType { get; set; }
    }

Linq Query

List<Customer> customers = new Customer().Getcustomers();
            var results = from cust in customers
                          group cust by new
                          {

                              cust.City,
                              cust.ProductType
                          } into gp
                          select new
                          {
                              ProductType = gp.Key.ProductType,
                              City = gp.Key.City,
                              TotalOrderAmount = gp.Sum(item => item.OrderAmount)
                          };
            foreach (var r in results)
            {
                Debug.Print(($@"ProductType:""{r.ProductType}"",City:""{r.City}"",TotalOrderAmount:""{r.TotalOrderAmount}"""));
            }

Output:

ProductType:"Clothing",City:"London",TotalOrderAmount:"2000"
ProductType:"Jewelry",City:"Paris",TotalOrderAmount:"1800"
ProductType:"Household",City:"Sydney",TotalOrderAmount:"2400"
ProductType:"Health",City:"Paris",TotalOrderAmount:"4300"
ProductType:"Jewelry",City:"London",TotalOrderAmount:"3600"
ProductType:"Clothing",City:"Tokyo",TotalOrderAmount:"1400"

Example 2:

Let’s take one more example of LINQ to group by multiple properties and sum, we have a list of doctor objects, Now I want to group the doctor list based on the Gender and SpecializationId, and return an object containing the Gender, SpecializationId, and the TotalSalary.

Table doctor

Output

Linq Query

var results = from doc in db.TblDoctors
                          group doc by new
                          {
                              doc.Gender,
                              doc.SpecializationId
                          } into gp
                          select new
                          {
                              Gender = gp.Key.Gender,
                              SpecializationId = gp.Key.SpecializationId,
                              TotalSalary = gp.Sum(item => item.Salary)
                          };
            foreach (var r in results)
            {
                Debug.Print(($@"SpecializationId:""{r.SpecializationId}"",Gender:""{r.Gender}"",TotalSalary:""{r.TotalSalary}"""));
            }

Example 3:

I have a product sales table that looks like the one below, Now I want to group the product list based on the Month and Year of datesold, and return an object containing the SoldDate, Product,Qty, CostPrice, and the ShipingCost.

Product sales table

we need to make the SUM of Qty,CostPrice and ShipingCost each month so our output table would look like this:

Output

public class Product
    {
        public string Name { get; set; }
        public int Qty { get; set; }
        public int CostPrice { get; set; }
        public int ShipingCost { get; set; }
        public DateTime SoldDate { get; set; }
        public List<Product> GetProducts()
        {
            var objList = new List<Product>();
            objList.Add(new Product() { Name = "Computers", Qty = 5, CostPrice = 800, ShipingCost = 20, SoldDate = new DateTime(2022, 01, 15) });
            objList.Add(new Product() { Name = "Sports", Qty = 3, CostPrice = 500, ShipingCost = 20, SoldDate = new DateTime(2022, 01, 15) });
            objList.Add(new Product() { Name = "Cameras", Qty = 2, CostPrice = 400, ShipingCost = 20, SoldDate = new DateTime(2022, 01, 15) });
            objList.Add(new Product() { Name = "furniture", Qty = 4, CostPrice = 250, ShipingCost = 20, SoldDate = new DateTime(2022, 02, 24) });
            objList.Add(new Product() { Name = "Sports", Qty = 6, CostPrice = 500, ShipingCost = 20, SoldDate = new DateTime(2022, 03, 24) });
            objList.Add(new Product() { Name = "furniture", Qty = 6, CostPrice = 250, ShipingCost = 20, SoldDate = new DateTime(2022, 03, 26) });
            objList.Add(new Product() { Name = "Cameras", Qty = 8, CostPrice = 400, ShipingCost = 20, SoldDate = new DateTime(2022, 04, 26) });
            objList.Add(new Product() { Name = "Computers", Qty = 9, CostPrice = 800, ShipingCost = 20, SoldDate = new DateTime(2022, 04, 27) });
            return objList;
        }
    }
    

Linq Query

var results = from p in products
                          let k = new
                          {
                              MonthYear = p.SoldDate.ToString("MMM-yy"),
                              Product = p.Name
                          }
                          group p by k into t
                          select new
                          {
                              MonthYear = t.Key.MonthYear,
                              Product = t.Key.Product,
                              Qty = t.Sum(p => p.Qty),
                              CostPrice = t.Sum(p => p.CostPrice),
                              ShipingCost = t.Sum(p => p.ShipingCost)
                          };
            foreach (var r in results)
            {
                Debug.Print(($@"SoldDate:""{r.MonthYear}"",Product:""{r.Product}"",Qty:""{r.Qty}"",CostPrice:""{r.CostPrice}"",ShipingCost:""{r.ShipingCost}"""));
            }

*If you have any query ,please comment,Thank you

The post [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax appeared first on Software Development | Programming Tutorials.