CODEWITHSUNDEEP
c#linq
onedevteam.com
onedevteam.com

Reputation: 4178

Sum and grouping multiple columns into one value in linq

I have table with fields:

ID,sales1, sales2, sales3
1   25      15       15
1   10      7        3
2   8       11       9
3   10      5        7
3   22      9        4

I need to filter table by id and return sum of sales1 + sales2 + sales3. In mysql this would be done with 

SELECT (SUM(sales1) + SUM(sales2) + SUM(sales3)) AS total
WHERE id = 1
GROUP BY id

but i can't get this to work in linq. Can someone help me with this?

Upvotes: 1

Views: 1118

Answers (2)

Marco
Marco

Reputation: 23945

Scratched together in LinqPad:

void Main()
{
    //since I don't have a suitable database at hand
    //I'm emulating with a list
    var sales = new List<Sale>()
    {
        new Sale() {Id = 1, Sales1 = 25, Sales2= 15, Sales3= 15},
        new Sale() {Id = 1, Sales1 = 10, Sales2= 7, Sales3= 3},
        new Sale() {Id = 2, Sales1 = 8, Sales2= 11, Sales3= 9},
        new Sale() {Id = 3, Sales1 = 10, Sales2= 5, Sales3= 7},
        new Sale() {Id = 3, Sales1 = 22, Sales2= 9, Sales3= 4},
    };

    var filteredSales = sales.GroupBy (s => s.Id)//Need to Group to be able to use .Sum() 
                             //.Where (s => s.Key == 1)
                             .Select (s => new
                              {
                               Id = s.Key,
                               Sum = s.Sum (x => x.Sales1 + x.Sales2 + x.Sales3) 
                              }).Dump(); //ignore Dump. Only available in LinqPad
}

class Sale
{
    public int Id { get; set; }
    public int Sales1 { get; set; }
    public int Sales2 { get; set; }
    public int Sales3 { get; set; }
}

Result:

Id | Sum
1    75
2    28
3    57

Uncomment the where clause to filter by id. Kind regards

Upvotes: 5

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

var result =
table.GroupBy(x=>x.ID)
.Where(x=>x.Key==1)
.Select(x=>new {total=x.Sum(s=>s.sales1)+x.Sum(s=>s.sales2)+x.Sum(s=>s.sales3)})

Upvotes: 2

Related Questions