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#
- Given a DateTime object, how do I get an ISO 8601 date in string format?
- how get yesterday and tomorrow datetime in c#
- How to get File Created Date and Modified Date
- Join Date and Time to DateTime in C#
- Loop through an object's properties and get the values for those of type DateTime
- How can I get date and time formats based on Culture Info?
- Get short date for System Nullable datetime (datetime ?) in C#
- Combine date and time when date is a DateTime and time is a string
- JSON Date and DateTime serialisation in c# & newtonsoft
- Get members of Active Directory Group and check if they are enabled or disabled
- Get Date Time Hours and Minutes with leading Zero
- How to get client date and time in ASP.NET?
- c# get start-date and last-date based on current date
- How to GET data from an URL and save it into a file in binary in C#.NET without the encoding mess?
- How do I convert DateTime .NET datatype to W3C XML DateTime data type string and back?
- Get a DateTime by subtracting seconds from the current date
- Why is there no DateTime.AddWeeks(), and how can I get a DateTime object for 52 weeks ago?
- get DateTimeOffset from DateTime (utc) and TimeZoneInfo
- Get only the Date without Time from DateTime
- How do define get and set for an array data member?
- Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#
- Get minimum and maximum value using linq
- not being able to convert from FILETIME (windows time) to dateTime ( I get a different date )
- How to decode a string of text from a Base64 to a byte array, and the get the string property of this byte array without data corruption
- How to get the Date time month start and End Date?
- Using a WebClient and C#, how do I get returned data even when the response is (400) Bad Request?
- How to get the value of cell containing a date and keep the original formatting using NPOI
- Get date of first and last day of week knowing week number
- Using Linq and XDocument, can I get all the child elements under parent tag?
- Get current date time from server and convert it into local time in c#
- how does form authentication work in asp.net?
- mongo C# remove multiple records by id
- <Link> in Helper method gives "Element link cannot be nested within element 'link'"
- .Net DefaultValueAttribute on Properties
- build .net solution from batch file
- Authorization in ASP .NET Core Razor pages
- What is the default value of the nullable type "int?" (including question mark)?
- IoC container mappings: singleton vs each-call creation
- Why does DirectoryInfo.GetFiles() match files that don't match the mask?
- Chaining DebuggerDisplay on complex types
- How to install Automapper for .net 3.5
- Using XAML resource in view model
- C# to format (indent, align) C# properly
- Metaprograming in C# : Automatic ToString Method
- How do you refactor two classes with the same, duplicated events?
- How to make inline array initialization work like e.g. Dictionary initialization?
- Performance issue with generation of random unique numbers
- C# Encoding.Converting Latin to Hebrew
- Should I test that methods don't throw exceptions?
- How to copy a file to another path?