How to sum data by month in LINQ?
I can choose the amount by year, which is easy.
Just.
var query = from t in ctx.SomeDataEntity
group t by t.DateAdded.Year into g
select new
{
Year = g.Year,
Total = g.Sum(t => t.SomeColumn1) +
g.Sum(t => t.SomeColumn2) +
g.Sum(t => t.SomeColumn3) +
g.Sum(t => t.SomeColumn4)
};
But how do you filter the data every month? It's not as easy as just replacing t.DateAdded.Year with t.DateAdded.Month, calling t.DateAdded.Month - 1,2,3, ..., 12. I need it to be in 2014-01 format , 2014-02, ..., 2014-12.
+3
source to share
3 answers
You can group both Year and Month as follows:
var query = from t in ctx.SomeDataEntity
group t by new
{
Year = t.DateAdded.Year,
Month = t.DateAdded.Month
} into g
select new
{
MonthAndYear = g.Key.Year + "-" + g.Key.Month,
Total = g.Sum(t => t.SomeColumn1) +
g.Sum(t => t.SomeColumn2) +
g.Sum(t => t.SomeColumn3) +
g.Sum(t => t.SomeColumn4)
};
+1
source to share
You can group both month and year:
var query = from t in ctx.SomeDataEntity
group t by new { Month = t.DateAdded.Month, Year = t.DateAdded.Year } into g
select new
{
Year = g.Key.Year,
Month = g.Key.Month,
Total = g.Sum(t => t.SomeColumn1) +
g.Sum(t => t.SomeColumn2) +
g.Sum(t => t.SomeColumn3) +
g.Sum(t => t.SomeColumn4)
};
0
source to share
Try the following:
var query = from t in ctx.SomeDataEntity
group t by new { t.DateAdded.Year, t.DateAdded.Month} into g
select new
{
Year = g.Key.Year,
Month = g.Key.Month,
Total = g.Sum(t => t.SomeColumn1) +
g.Sum(t => t.SomeColumn2) +
g.Sum(t => t.SomeColumn3) +
g.Sum(t => t.SomeColumn4)
};
0
source to share