19

I am having a table structure with columns

FeesNormal

FeesCustom

Currency

Now i am looking for a SUM function group by currency .

For example 20 USD + 30 EURO + 40 INR something like this from this table

I also have to consider the scenario if FeesCustom > 0 I have to ignore FeesNormal for the row

Sample date and expected result is like this 

 FeesNormal FeesCustom Currency 10 0 USD 15 25 USD //in this case can ignore FeesNormal Since FeesCustom is more 5 10 EUR //same for this row ignore FeesNormal 10 0 EUR Expected result 35 USD 20 EUR

I able to find sum using the linq

 int sum_custom=(int)fee_list.Where(p => p.FeesCustom > 0).Sum(p => p.FeesCustom); int sum_normal = (int)fee_list.Where(p => p.FeesCustom ==0).Sum(p => p.FeesNormal);
Improve this question
5
  • Do you want your condition to be FeesCustom > 0 or FeesCustom != 0? Commented May 20, 2016 at 6:04
  • FeeCustom > 0 Then consider FeeCustom in Sum else consider FeeNormal n Sum Commented May 20, 2016 at 6:05
  • So your final line of code should really be p.FeesCustom <= 0, right? To be the opposite of the condition of > 0. Commented May 20, 2016 at 6:07
  • Logic is clear from your answer I think it should be like in answer like if FeeCustom>0 use FeeCustom else FeeNormal Commented May 20, 2016 at 6:12
  • That's what my answer has, yes. I'm saying that your question isn't as clear as it could be. Commented May 20, 2016 at 6:13

3 Answers 3

Reset to default
44

It seems to me that you just need a projection from "entry" to "effective fee" which you can sum - something like:

var result = source .GroupBy(x => x.Currency) .Select(g => new { Currency = g.Key, Total = g.Sum(x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal) });

That's equivalent to:

var result = source .GroupBy(x => x.Currency, (key, values) => new { Currency = key, Total = values.Sum(x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal) });

Or do the transformation earlier:

 var result = source .Select(x => new { x.Currency, x.Fee = x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal }) .GroupBy(x => x.Currency, x => x.Fee, (key, values) => new { Currency = key, Fee = values.Sum() });
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

To complete my answer i have to follow one more operation and here is the link for that stackoverflow.com/questions/37339611/union-of-2-list-by-groupby
4

Using Query Syntax:

var feeResult = (from fee in fee_list group fee by fee.Currency into groupResult select new { Currency = groupResult.Key, FinalFees = groupResult.Sum(f => f.FeesCustom > 0 ? f.FeesCustom : f.FeesNormal) }).ToList();
Improve this answer

Comments

2

Assuming you have DataTable with the mentioned data, you could do this using Linq

var result = table.AsEnumerable() .GroupBy(x=> x.Field<string>("Currency")) .Select(x=> new { Currency = x.Key, Value = x.Sum(s=> Math.Max(s.Field<double>("FeesNormal"), s.Field<double>("FeesCustom "))), } .ToList()
Improve this answer

5 Comments

How about handling the condition of CustomAmount ? is it possible to do it on single query ?
What is CustomAmount? can you elaborate more?
Why assume a datatable when the code in the question doesn't use one? It seems to make things more complicated for no reason... (I also hope the OP isn't using double values for currency...)
Question says that table with columns, that's why I assumed it is datatable.
Well using LINQ to SQL or EF would still be talking about a table, but would make more sense with the sample code given. (Personally I hope that using DataTable is pretty rare these days.)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.