Tavakkoli Tavakkoli - 6 months ago 78
SQL Question

How to Calculate Subtotal for each item from datagridview?

I have table like this :

NAME ITEM COUNT
a x 2
a y 1
b x 3
c z 1
d y 1
d y 1


I have used this code to calculate total

double sum = 0;
for (int i = 0; i < dataGridView1.Rows.Count; ++i)
{
sum += Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
}


How can I calculate sum for each item separately, result should be:

x=5
y=3
z=1

Answer

Follow these steps:

1) Loop through the datagrid.

2) Identify similar Item(like x,y and z) in loop and sum those.

int SumX=0;
int SumY=0;
int SumZ=0;
for (int i = 0; i < dataGridView1.Rows.Count; ++i)
{
if(Convert.ToString(dataGridView1.Rows[i].Cells[1].Value == "x")
   sumX += Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
else if(Convert.ToString(dataGridView1.Rows[i].Cells[1].Value == "y")
   sumY += Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
else if(Convert.ToString(dataGridView1.Rows[i].Cells[1].Value == "z")
   sumZ += Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
}

Here is an example.

Using LINQ query it's fairly straightforward.

int SumX = dataGridView1.Rows.Cast<DataGridViewRow>()
                    .Where(r=> Convert.ToInt32(r.Cells["Item"].Value) == "x")
                    .Sum(t=> Convert.ToInt32(t.Cells["Count"].Value));

Edit

If you really want to make this summation dynamic then you can do something like this.Basically here is a dictionary to keep track of identical ITEM(s) and then sum the corresponding count.

    Dictionary<string, int> dic = new Dictionary<string, int>();
    string item = null;
    for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)
    {
            item = dataGridView1.Rows[i].Cells[1].Value.ToString();
            if (!dic.ContainsKey(item))
            {
                dic.Add(item, Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
            }
            else
            {
                dic[item] += Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
            }

    }

Now you can loop through the dictionary and get the unique items count.

    foreach (KeyValuePair<string, int> keyvalue in dic)
    {
       //get it here
    }    

Hope this helps you.