0

I am using this query to calculate the sum of amount and display it in a label. Just because I am using in string it doesn't display values? Any suggestions?

string a;

var query = from r in dt.AsEnumerable()
            where r.Field<string>("Code") == strCode
            select decimal.Parse(
                r.Field<string>("Amount")
                .Replace("$", "")
                .Replace(",", "")
            );

if (query.Count() == 0)
{
    a = "0";
}
else
{
    foreach (var item in query)
    {
        a = item.ToString();
    }
}

return a;
  • 5
    What is the exact problem you are having? The two I can see at a glance are that you aren't calculating the sum anywhere and that you are repeatedly overwriting the value of a in your loop. Where exactly is your problem and what are you trying to achieve? – Chris Mar 31 '14 at 10:32
  • 1
    The values are not changing to decimal for dollars. –  Mar 31 '14 at 10:34
  • So it is unable to convert whatever values are in your field into decimal values? If so you will have to give us some examples of strings that you have in that Amount column so that we can better explain why they aren't converting. – Chris Mar 31 '14 at 10:35
  • Also `a = query.Sum().ToString()` should replace your if statement quite happily. – Chris Mar 31 '14 at 10:36
  • 1
    The Amount column will have values like this $1540.00, $1320.30, $7800.45 –  Mar 31 '14 at 10:38
  • And have you tried outputting them as a string list so you can confirm that they definitely are in a valid format and that, for example, if there is no amount it hasn't got "n/a" or something else like that in? – Chris Mar 31 '14 at 10:55
  • @Vicky if you're using C#, isn't this something that you could have just debugged in Visual Studio to figure out why this wasn't working? –  Mar 31 '14 at 12:55
  • @Vicky if you found my answer helpful, please consider upvoting it as well, it really helps people when you do. –  Apr 01 '14 at 17:16

2 Answers2

1

Parsing dollar amounts

The NumberStyles enumeration from System.Globalization can be used to parse dollar amounts into decimals:

decimal.Parse(r.Field<string>("Amount"), NumberStyles.Currency)

See also Problem parsing currency text to decimal type.

Summing dollar amounts

Also, your for-loop isn't actually adding any of the amounts, it's just assigning each amount to the a variable, so that a is only equal to the last amount at the end:

foreach (var item in query)
{
    a = item.ToString();
}

return a;

What you really want to do is to add the amounts:

decimal a = 0;

foreach (var item in query)
{
    a = a + item;
    // Or even shorter:
    a += item;
}

return a.ToString();

Summing with LINQ

But LINQ provides the method Sum() which can be used to replace the for-loop altogether. Also, when Sum() is called on an empty-set of decimals, it already returns 0, so you don't need your if block that checks if the count is 0:

// Don't need this IF block
if (query.Count() == 0)
{
    a = "0";
}

return query.Sum().ToString();

Altogther now...

So putting it all together, you get the code below:

// Top of file...
using System.Globalization;

// In your method...
var query =
    from r in dt.AsEnumerable()
    where r.Field<string>("Code") == strCode
    select decimal.Parse(r.Field<string>("Amount"), NumberStyles.Currency);

return query.Sum().ToString();
Community
  • 1
  • 1
-1

Try this,

You need to use Sum to get the total amount

string TotalSum = "";
var query = from r in dt.AsEnumerable()
        where r.Field<string>("Code") == strCode
        select decimal.Parse(r.Field<string>("Amount"), System.Globalization.NumberStyles.Any);
if (query.Count() > 0)
{
TotalSum = string.Format("{0:C}", query.Sum());
}
Label1.Text = TotalSum;
Learner
  • 353
  • 9
  • 37
  • 1
    Any reason for the if? `"0"` would be the same as `query.ToString()` if `query==0` so you are doing exactly the same thing in both branches... Also from comments on the question it appears the problem might be with the parsing rather than the summing. Oh, and you should actually explain what changes you made to the code and why to make sure the OP understands why your code changes have fixed the problem (ie call out the `.Sum()` explicitly... – Chris Mar 31 '14 at 10:50