C# 多个 group by 和 Sum LINQ
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1541030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple group by and Sum LINQ
提问by Luiscencio
I have a products sales table that looks like this:
我有一个产品销售表，如下所示：
saleDate prod qty
10/22/09 soap 10
09/22/09 pills 05
09/25/09 soap 06
09/25/09 pills 15
I need to make the SUM of each MONTH so the final table would look like this:
我需要计算每个 MONTH 的 SUM，以便最终表如下所示：
saleDate prod qty
10/09 soap 10
09/09 soap 06
09/09 pills 20
Can I do this with LINQ?
我可以用 LINQ 做到这一点吗？
采纳答案by Matthew Whited
var products = new[] {
new {SaleDate = new DateTime(2009,10,22), Product = "Soap", Quantity = 10},
new {SaleDate = new DateTime(2009,09,22), Product = "Pills", Quantity = 5},
new {SaleDate = new DateTime(2009,09,25), Product = "Soap", Quantity = 6},
new {SaleDate = new DateTime(2009,09,25), Product = "Pills", Quantity = 15}
};
var summary = from p in products
let k = new
{
//try this if you need a date field
// p.SaleDate.Date.AddDays(-1 *p.SaleDate.Day - 1)
Month = p.SaleDate.ToString("MM/yy"),
Product = p.Product
}
group p by k into t
select new
{
Month = t.Key.Month,
Product = t.Key.Product,
Qty = t.Sum(p => p.Quantity)
};
foreach (var item in summary)
Console.WriteLine(item);
//{ Month = 10/09, Product = Soap, Qty = 10 }
//{ Month = 09/09, Product = Pills, Qty = 20 }
//{ Month = 09/09, Product = Soap, Qty = 6 }
回答by Cam Soper
Sure.
当然。
It'll look like this:
它看起来像这样：
var GroupedSales =
from p in products
group p by p.saleDate.Month into g
select new { saleMonth = g.saleDate.Month, QtySold = g.Sum(p => p.qty) };
See: http://msdn.microsoft.com/en-us/vcsharp/aa336747.aspx#sumGrouped
请参阅：http: //msdn.microsoft.com/en-us/vcsharp/aa336747.aspx#sumGrouped
Also, note that I was operating under the assumption that your dataset only had 2009 data. If you want to group by month/year, you can use saleDate.ToString("yyyyMM") instead of saleDate.Month.
另请注意，我是在假设您的数据集只有 2009 年数据的情况下进行操作的。如果要按月/年分组，可以使用 saleDate.ToString("yyyyMM") 而不是 saleDate.Month。
回答by manji
var q = from s in sales
group s by new {saleDate = s.saleDate.ToString("MM/yy"), s.prod} into g
select new { g.Key.saleDate, g.Key.prod, qty = g.Sum(s => s.qty) };
回答by Jason Punyon
class Program
{
static void Main(string[] args)
{
var sales = new List<Sale>();
sales.Add(new Sale() { Product = "soap", saleDate = new DateTime(2009, 10, 22), Quantity = 10});
sales.Add(new Sale() { Product = "soap", saleDate = new DateTime(2009, 9,22), Quantity = 6});
sales.Add(new Sale() { Product = "pills", saleDate = new DateTime(2009,9,25), Quantity = 15});
sales.Add(new Sale() { Product = "pills", saleDate = new DateTime(2009,9,25), Quantity = 5});
var q = from s in sales
group s by new { s.Product, s.saleDate.Month, s.saleDate.Year } into g
select new {Month = String.Format("{0:MM/yy}", new DateTime(g.Key.Year, g.Key.Month, 1)), product = g.Key.Product, quantity = g.Sum(o=>o.Quantity)};
}
}
class Sale
{
public DateTime saleDate { get; set; }
public int Quantity { get; set; }
public string Product { get; set; }
}
