Remove From My Forums

sum values in datatable using linq based on conditions

Question
0

Sign in to vote
User-1888080761 posted

Hi,

I'm having a datatable like mentioned below.

ID Percentage 1 50 1 30 2 0 2 100

Result:

ID Percentage 1 80 2 100

I tried this and it doesn't work

var n = dt.AsEnumerable().Where(r => (int)r["ID"] != "0" || (int)r["ID"] != "100"). Sum(r => (int)r["Percentage"]);

Now I need to sum the percentage for each ID and the percentage for each ID should be 0 or 100 percentage.

If any one of the ID in table doesn't have 0 or 100 I need to alert. Pls suggest me how I can do this in linq and I think its the best way.
Thursday, April 23, 2015 3:59 AM

Anonymous

-

Points

Anonymous

Joined May 2018

Anonymous's threads

Show activity

- Points

Answers

User1577371250 posted

Hi,

try this

var result = (from p in table.AsEnumerable()
                          group p by p["ID"]
                              into r
                              select new
                              {
                                  ID = r.Key,
                                  Percentage = r.Sum((s) => int.Parse(s["Percentage"].ToString()))
                              }).ToList();

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Thursday, April 23, 2015 8:03 AM

Anonymous

Points

Anonymous
	Joined May 2018

	Anonymous's threads Show activity

- Points

All replies

User1577371250 posted

Hi,

Check this query.

DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("Percentage", typeof(int));

            table.Rows.Add(1, 50);
            table.Rows.Add(1, 30);
            table.Rows.Add(2, 0);
            table.Rows.Add(2, 100);

            var result = (from p in table.AsEnumerable()
                          group p by p["ID"]
                              into r
                              select new
                              {
                                  ID = r.Key,
                                  Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                              }).ToList();

Thursday, April 23, 2015 4:10 AM

Anonymous

Points

Anonymous
	Joined May 2018

	Anonymous's threads Show activity

- Points

User-1888080761 posted

Hi,

Check this query.

DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("Percentage", typeof(int));

            table.Rows.Add(1, 50);
            table.Rows.Add(1, 30);
            table.Rows.Add(2, 0);
            table.Rows.Add(2, 100);

            var result = (from p in table.AsEnumerable()
                          group p by p["ID"]
                              into r
                              select new
                              {
                                  ID = r.Key,
                                  Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                              }).ToList();

Thanks for the prompt reply. How can I alert if the conditions get satisfied.

Thursday, April 23, 2015 4:14 AM

Anonymous

Points

Anonymous
	Joined May 2018

	Anonymous's threads Show activity

- Points

0

Sign in to vote

User1577371250 posted

Hi,

Can you post some sample data for that?

i didn't get your point here.

Thursday, April 23, 2015 4:15 AM

Anonymous

-

Points

Anonymous

Joined May 2018

Anonymous's threads

Show activity

- Points
0

Sign in to vote
User-657100335 posted

foreach (var item in result) { if (item.Percentage != 0 || item.Percentage != 100) MessageBox.Show("Your alert here"); }

Try adding this after Lokesh's answer for the alert.
Thursday, April 23, 2015 4:22 AM

Anonymous

-

Points

Anonymous

Joined May 2018

Anonymous's threads

Show activity

- Points
0

Sign in to vote
User-1888080761 posted

Hi,

Can you post some sample data for that?

i didn't get your point here.

The below is the result we get after checking the condition.

If sum of any of the ID doesn't have 0 or 100 then I need to alert. Also can ID be considered as string.

ID Percentage 1 80 2 100
Thursday, April 23, 2015 5:10 AM

Anonymous

-

Points

Anonymous

Joined May 2018

Anonymous's threads

Show activity

- Points

User1577371250 posted

try this

 DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(string));
            table.Columns.Add("Percentage", typeof(int));

            table.Rows.Add("1", 50);
            table.Rows.Add("1", 30);
            table.Rows.Add("2", 0);
            table.Rows.Add("2", 100);

            var result = (from p in table.AsEnumerable()
                          group p by p["ID"]
                              into r
                              select new
                              {
                                  ID = r.Key,
                                  Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                              }).ToList();


            foreach (var item in result)
            {
                if (item.Percentage != 0 || item.Percentage == 100)
                {
                    string message = item.ID + " percentage is " + item.Percentage;
                    // Show the message in ALERT
                }
            }

Thursday, April 23, 2015 5:27 AM

Anonymous

Points

Anonymous
	Joined May 2018

	Anonymous's threads Show activity

- Points

User-1888080761 posted

try this

 DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(string));
            table.Columns.Add("Percentage", typeof(int));

            table.Rows.Add("1", 50);
            table.Rows.Add("1", 30);
            table.Rows.Add("2", 0);
            table.Rows.Add("2", 100);

            var result = (from p in table.AsEnumerable()
                          group p by p["ID"]
                              into r
                              select new
                              {
                                  ID = r.Key,
                                  Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                              }).ToList();


            foreach (var item in result)
            {
                if (item.Percentage != 0 || item.Percentage == 100)
                {
                    string message = item.ID + " percentage is " + item.Percentage;
                    // Show the message in ALERT
                }
            }

I think its close. Can I have percentage as int?

Thursday, April 23, 2015 5:37 AM

Anonymous

Points

Anonymous
	Joined May 2018

	Anonymous's threads Show activity

- Points

0

Sign in to vote
User1577371250 posted

Hi,

Can I have percentage as int?

It's already INT.

table.Columns.Add("Percentage", typeof(int));
Thursday, April 23, 2015 5:52 AM

Anonymous

-

Points

Anonymous

Joined May 2018

Anonymous's threads

Show activity

- Points

User-1888080761 posted

Hi,

vignesht5
Can I have percentage as int?

It's already INT.

            table.Columns.Add("Percentage", typeof(int));

In this part I need to have percentage as int.

 select new
                              {
                                  ID = r.Key,
                                  Percentage = r.Sum((s) => decimal.Parse(s["Percentage"].ToString()))
                              }).ToList();

Thursday, April 23, 2015 5:57 AM

Anonymous

Points

Anonymous
	Joined May 2018

	Anonymous's threads Show activity

- Points

User1577371250 posted

Hi,

try this

var result = (from p in table.AsEnumerable()
                          group p by p["ID"]
                              into r
                              select new
                              {
                                  ID = r.Key,
                                  Percentage = r.Sum((s) => int.Parse(s["Percentage"].ToString()))
                              }).ToList();

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Thursday, April 23, 2015 8:03 AM

Anonymous

Points

Anonymous
	Joined May 2018

	Anonymous's threads Show activity

- Points

Answered by:

sum values in datatable using linq based on conditions

Question

Answers

All replies

vignesht5