LINQ group by datetime and sum | Linq get sum of data group by date

In this article, we will learn how to use LINQ to group by DateTime and perform summation operations using C#. We assume that you are familiar with SQL queries and the GROUP BY clause. If not, please refer to the following articles:

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

I have a list of products purchased on a particular date. I want to group them by the date of sale, summing the quantity and cost price for each day. Essentially, we aim to group the items based on the month and year of the purchase date.

Order Table

Output

I created a products class which looks like the below:

    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 products = new Product().GetProducts();
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}"
""));
}

The code then constructs a LINQ query using a let clause to define a temporary anonymous type k.

MonthYear is derived from the SoldDate property of each product, formatted to display the month abbreviation and year (e.g., "Jan-22"). Inside the select clause, it projects the grouped results into a new anonymous object and this new object contains properties  MonthYear, Product, Qty (the total quantity of products sold), CostPrice (the total cost price of products sold), and ShipingCost (the total shipping cost).

Using above code we are getting sales data for products, grouping them by month and year of sale along with their associated details such as quantity sold, cost price, and shipping cost.

Linq get sum of data group by date

 List<Product> products = new List<Product>
        {
new Product { Id = 1, Name = "Product A", SoldDate = DateTime.Parse("2024-03-10"), Quantity = 5, Price = 10.5m },
new Product { Id = 2, Name = "Product B", SoldDate = DateTime.Parse("2024-03-10"), Quantity = 3, Price = 15.75m },
new Product { Id = 3, Name = "Product C", SoldDate = DateTime.Parse("2024-03-11"), Quantity = 2, Price = 8.25m },
new Product { Id = 4, Name = "Product A", SoldDate = DateTime.Parse("2024-03-11"), Quantity = 4, Price = 12.0m }
        };

var results = from p in products
                      group p by p.SoldDate.Date into g
                      select new
                      {
                          SoldDate = g.Key,
                          TotalQuantity = g.Sum(p => p.Quantity),
                          TotalPrice = g.Sum(p => p.Price)
                      };

foreach (var result in results)
        {
            Console.WriteLine($"Date: {result.SoldDate.ToShortDateString()}, Total Quantity: {result.TotalQuantity}, Total Price: {result.TotalPrice:C}");
        }

var results = from p in products
              group p by new { p.SoldDate.Date, p.Id } into g
              select new
              {
                  SoldDate = g.Key.Date,
                  ProductId = g.Key.Id,
                  TotalAmount = g.Sum(p => p.Quantity * p.Price)
              };

foreach (var result in results)
{
    Console.WriteLine($"Date: {result.SoldDate.ToShortDateString()}, Product ID: {result.ProductId}, Total Amount: {result.TotalAmount:C}");
}

Bunch By section is utilized to put every one of the columns with a similar worth. These qualities are of that predefined section in one gathering. It connotes that all lines will put an equivalent sum through a solitary section, which is of one suitable segment in one gathering.

In LINQ, The Group By articulation is utilized for getting sorted out comparative information into gatherings. The information is additionally coordinated with the assistance of a comparable capacity. That is to say, that assuming that various lines in an exact segment have similar qualities, it will organize those columns in a gathering.

The total capacities permit you to play out the estimation of a bunch of lines and return a solitary worth. The GROUP BY proviso is frequently utilized with a total capacity to perform estimations and return a solitary incentive for every subgroup.

For instance, if you need to know the quantity of orders in every status, you can utilize the COUNT work with the GROUP BY statement as follows:

By and large, DISTINCT proviso is an extraordinary instance of the GROUP BY condition. The contrast between DISTINCT proviso and GROUP BY condition is that the GROUP BY statement sorts the outcome set, while the DISTINCT proviso doesn’t.