Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I retrieve a sum of distinct values to list?

The SQL table and model supporting my application is very similar to what I have displayed below.

Id | CustomerName | FruitName | Charge
_____________________________________
1  | Bob          | Banana    | 3.00 
2  | Jill         | Apple     | 2.00
3  | Bob          | Apple     | 3.00
4  | Marvin       | Banana    | 1.00
5  | Sam          | Pear      | 4.00

[Key]
public int Id {get; set;}
public string CustomerName { get; set; }
public string FruitName { get; set;}
public string Charge {get; set;}

What I would like to do is pull the distinct FruitName and the Charge.Sum() and display it ToList() in my view. Here is what I have tried

var fruitToList = (from f in db.fruit select f).ToList();    
var test = fruitToList.GroupBy(x => x.FruitName).Sum(y => y.Charge).Select(z => z.First());
return View(test.ToList());

I know that the reason why it does not work is because Charge needs to be something like decimal.Parse(y.Charge) but I cannot figure out how to do it within the Sum() function. Should I be going about this a different way?

like image 694
Skullomania Avatar asked Mar 03 '23 15:03

Skullomania


1 Answers

Weird that the charge is a string, but never the less, since the data is already in a list you can group by fruit name extract the charge as a decimal and sum the group

For example

//...

var test = fruitToList
    .GroupBy(_ => _.FruitName, _ => new { Charge = decimal.Parse(_.Charge) })
    .Select(g => new Fruit {
        FruitName = g.Key,
        Charge = g.Sum(f => f.Charge).ToString()
    });

//...

Which could also be simplified even more

var test = fruitToList
    .GroupBy(_ => _.FruitName, _ => decimal.Parse(_.Charge))
    .Select(g => new Fruit {
        FruitName = g.Key,
        Charge = g.Sum().ToString()
    });
like image 73
Nkosi Avatar answered Mar 12 '23 01:03

Nkosi