summing from a datagrid

Question

Hy, 

here's the problem. I got a table and what I want to do is to make a sum, but only for a single person in that table, not all of them. etc.

Table

ID             First Name             FamilyName                  Amount

------------------------------------------------------------------------------

1 John Smith 500

1 John Smith 250

2 Alison  White 1000

3 Andrew Kazinsky 300

 

With my code:

 

decimal zbroj = 0;
            foreach (DataGridViewRow row in this.dataGridView1.Rows)
            {
                DataGridViewCell cell = row.Cells[5];
                {
                    if (cell.Value != null)
                    {
                        zbroj += Convert.ToDecimal(cell.Value);                       
                    }
                }
            }
            tbIznosBrutoDodatkaOdbitka.Text = zbroj.ToString();

I get a sum of 2050, and what I need is to get 750 (only for John Smith).

 

Thx for any help.

Answer 1

Try this way!

 

DataTable yourDatatabale= //your table containing values
           
            DataTable distinctNames= yourDatatabale.DefaultView.ToTable(true,"First Name ");

            for (int i = 0; i < distinctNames.Rows.Count; i++)
            {
                object sum = yourDatatabale.Compute("Sum(amount)", "name = '" + distinctNames.Rows[i]["name"] + "'");
            }
Or for case of  John Smith 

object sum = yourDatatabale.Compute("Sum(amount)", "name = 'John Smith'");

 

Answer 2

I'm afraid this doesn't work. Is this supposed to be solved over indexes?  

Answer 3

Do you try this.?What exception you are getting?
For loop in above code will give you complete sum group by first name.You can store them in list or any where.

Answer 4

Try below code,

var amounts = from person in Person.AsEnumerable()
                where Convert.ToInt32(person["Id"]) == 1
                select Convert.ToDecimal(person["Amount"]);

decimal sumJohnSmith = amounts.Sum();

I have assumed your table name is Person.

---

Please mark this post as answer if it solved your problem. Happy Programming!

Answer 5

No, my table is filled from SQL View (3 tables combined). Probably I haven't make myself clear enough, I do not need sum only for John Smith, but for each person, even if that person is placed in multiple rows. 

Something like this:

Table

ID FirstName FamilyName Amount

-----------------------------------------------------------------------------------------

1 a b 100

1 a b 150

1 a b 115

2 c d 150

2 c d 100

3 e f 200

3 e f 175

 

So, what I need is, when I click on person with ID = 1 to see sum in the textBox, BUT only where ID = 1,  sum = 365, and if I click etc. on person with ID = 2 to see sum = 250.

 

Thx!

Answer 6

That can help.

 

object sum = yourDatatabale.Compute("Sum(amount)", "ID= '"+YourDataGridView.SelectedRows[0].Cells[" ID
"].Value.ToString();+"'");

Answer 7

"Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index"

Answer 8

Try this code

 

decimal zbroj = 0;
string sFirstName="John";
string sFamilyName="Smith";

            foreach (DataGridViewRow row in this.dataGridView1.Rows)
            {
                DataGridViewCell cell = row.Cells[5];
                {
if(sFirstName=row.Cells[3].Value && sFamilyName=row.Cells[4].Value)
{
                    if (cell.Value != null)
                    {
                        zbroj += Convert.ToDecimal(cell.Value);                       
                    }
}
                }
            }
            tbIznosBrutoDodatkaOdbitka.Text = zbroj.ToString();

---

Happy Coding, RDRaja

Answer 9

Hi davor,

Try this:

DataGridViewTextBoxColumn Id, FirstName, LastName, Amount;
        DataTable dt;
        object zbroj;
        private void Form1_Load(object sender, EventArgs e)
        {
            Id = new DataGridViewTextBoxColumn();
            Id.Name = "Id";
            Id.DataPropertyName = "Id";
            dataGridView1.Columns.Add(Id);

            FirstName = new DataGridViewTextBoxColumn();
            FirstName.Name = "FirstName";
            FirstName.DataPropertyName = "FirstName";
            dataGridView1.Columns.Add(FirstName);

            LastName = new DataGridViewTextBoxColumn();
            LastName.Name = "FamilyName";
            LastName.DataPropertyName = "FamilyName";
            dataGridView1.Columns.Add(LastName);

            Amount = new DataGridViewTextBoxColumn();
            Amount.Name = "Amount";
            Amount.DataPropertyName = "Amount";
            dataGridView1.Columns.Add(Amount);
         
            loadPersons();
            dataGridView1.CellEnter += new DataGridViewCellEventHandler(dataGridView1_CellEnter);
        }

        private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            zbroj = dt.Compute("Sum(Amount)", "Id = '" + dataGridView1.CurrentRow.Cells[Id.Name].Value.ToString() + "' ");
            textBox1.Text = zbroj.ToString();
        }
        private void loadPersons()
        {
            SqlConnection conConnect = new SqlConnection("Data Source= (local); Database='Persons'; Integrated Security=true");
            SqlDataAdapter dAdapter = new SqlDataAdapter("Select * From Persons;", conConnect);
            DataSet DS = new DataSet();
            dAdapter.Fill(DS, "dsPersons");
            dataGridView1.AutoGenerateColumns = false;
            dt = DS.Tables["dsPersons"];
            dataGridView1.DataSource = dt;
        }

Hope it helps.

Answer 10

GridControl of devepxress have this functions!

---

Any fool can know. The point is to understand.(Albert Einstein)