In this article, we will learn to use LINQ group by DateTime and sum using C#. We have already learned group by in SQL query.
I’m presuming that you are knowledgeable with SQL queries and group by clause If not then please go through the below articles:
I have a list of products purchased on a specific date. I want to group them all into one date sold on the same day and then sum them Qty and CostPrice.Basically, we want to group the item based on the Month and Year of purchase date.
Order Table
ProductName | Qty | CostPrice | ShipingCost | SoldDate |
---|---|---|---|---|
Computers | 5 | 800 | 20 | 01/15/2022 0:00 |
Sports | 3 | 500 | 20 | 01/15/2022 0:00 |
Cameras | 2 | 400 | 20 | 01/15/2022 0:00 |
furniture | 4 | 250 | 20 | 02/24/2022 0:00 |
Sports | 6 | 500 | 20 | 03/24/2022 0:00 |
furniture | 6 | 250 | 20 | 03/26/2022 0:00 |
Cameras | 8 | 400 | 20 | 04/26/2022 0:00 |
Computers | 9 | 800 | 20 | 04/27/2022 0:00 |
Output
SoldDate | ProductName | Qty | CostPrice | ShipingCost |
---|---|---|---|---|
Jan-22 | Computers | 5 | 800 | 20 |
Jan-22 | Sports | 3 | 500 | 20 |
Jan-22 | Cameras | 2 | 400 | 20 |
Feb-22 | furniture | 4 | 250 | 20 |
Mar-22 | Sports | 6 | 500 | 20 |
Mar-22 | furniture | 6 | 250 | 20 |
Apr-22 | Cameras | 8 | 400 | 20 |
Apr-22 | Computers | 9 | 800 | 20 |
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}"""));
}
*If you have any query ,please comment,Thank you
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.
The post [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax appeared first on Software Development | Programming Tutorials.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- LINQ group by datetime and sum
- Get UTC DateTime by adding server date and UTC TimeSpan in c#
- Get DateTime object given local time as string, date as UTC DateTime and a timezone-locale string?
- group by name and order by date and each group order by date in linq
- C# Get data between date and hour from MySQL table
- Get date from table and display on datetime picker
- How to state timezone in DateTime to get date and month
- How to group linq expression only by date in datetime field
- Use Linq to group and organise list of data in correct order
- POST DATA , and get answer in c#
- How to identify and get the sqlserver data files filepath
- Unable to connect and get data from remote local Rest API with VPN proxy
- Get Intersect Date and Time from DateRange List
- Identify start and end of Data received by TCPClient and get complete data packet C#
- How to insert current date and time to SQL Server table that has a column with DateTime type?
- How can I eliminate certain values (zeros) and get the maximum value from some data group?
- Sorting the Dictionary with datetime key value to get smallest and biggest Key
- Making POST request to rest api and then making GET request to get additional data for the resource being added
- How to deserialize a JSON to generic objects and get the appropriate type based on the data structure in the JSON?
- Get Time in hours between to DateTime variables, but taking not more than 20 hours per date
- How to get all data from API using objects with Newtonsoft.Json and I receive wrong data in xamarin c#
- C# How to get data from the same form open several times and in each form there are different data?
- SQL : Get the duration between start DateTime and End DateTime from Table entries
- GroupBy does not seem to group by DateTime but rather just by Date
- Can't get the grid view data and store it to database
- How to get the list of week start date (Monday) and end date (Sunday) for the year in C#
- Can't get the grid view data and store it to database
- How to get the list of week start date (Monday) and end date (Sunday) for the year in C#
- How can I get all network requests and full response data when loading a page by Puppeteer-sharp?
- How to get a target date given a specific date after converting specific date from CET to IST and excluding weekends
- ASP.NET C# Async - Correct solution for background HTTPRequests?
- VSTS setting up Visual Studio Build task to do what command line does
- C# Trying to format datagridview data and pass back through the loop once formatted
- Load testing in Visual Studio. What is Error/sec attribute?
- Execute C# unit Tests in dll remotely without installing Visual Studio on remote machine
- Drop Down List In A Gridview
- c# Passing an object to a REST enabled WCF Service
- Send image from Ios application to web api service
- Custom DatePicker not working inside GridView
- Regular expression to replace dashes adjacent to complete numbers
- Unable to cast object of type 'System.Web.HttpCookie' to type 'System.String'.
- Having an object in a list know statistical information about that list
- Size of controls when anchoring/docking is problematic
- Client Server library for uploading files on C#?
- Asp.net - Can't Change Visibility of Panel After Downloading File
- Retrieve data from sql
- Error - cannot get value when using variable at datetimepicker name
- Toggle buttons on and off based on database value in C#
- Proxy in .net
- Convert Dictionary to c# object