Loop through datagridview and total amount of the same id

Question

I am trying to get the total amount for each ID on each date.

Example data:

ID                  Date        Amount
619|RENE ABERIA     2017-01-03  418.80
619|RENE ABERIA     2017-01-03  497.48
619|RENE ABERIA     2017-01-03  29.13
620|JAMES APRECIO   2017-01-03  460.70
620|JAMES APRECIO   2017-01-03  76.33
620|JAMES APRECIO   2017-01-04  460.70
620|JAMES APRECIO   2017-01-04  59.32
620|JAMES APRECIO   2017-01-06  460.70

This is all in a datagridview and have plenty of rows. For "619" on "2017-01-03" I have 3 different amounts e.g. 418.80, 497.48, and 29.13. I want to total this amount and place it below the last entry as shown below.

ID                  Date        Amount
619|RENE ABERIA     2017-01-03  418.80
619|RENE ABERIA     2017-01-03  497.48
619|RENE ABERIA     2017-01-03  29.13
                    Total:      945.41
620|JAMES APRECIO   2017-01-03  460.70
620|JAMES APRECIO   2017-01-03  76.33
                    Total:      537.03
620|JAMES APRECIO   2017-01-04  460.70
620|JAMES APRECIO   2017-01-04  59.32
                    Total:      520.02
620|JAMES APRECIO   2017-01-06  460.70
                    Total:      460.70

I think I have to use a FOR LOOP to go to each row but I don't know how to go about it or continue. Below is what I have so far and, obviously, it's still a long way to being complete.

 private void GetTotalShareForDay()
        {
        string laborerOrig = null;
        string laborerCopy = null;
        string dateOrig = null;
        string dateCopy = null;

        var share = 0.0;

        for (var i = 0; i < dgvSummary.Rows.Count; i++)
        {
            if (i == 0)
            {
                laborerOrig = dgvSummary.Rows[i].Cells[0].Value.ToString();
                dateOrig = dgvSummary.Rows[i].Cells[1].Value.ToString();
                laborerCopy = laborerOrig;
                dateCopy = dateOrig;

                share += Convert.ToDouble(dgvSummary.Rows[i].Cells[5].Value);
                Console.WriteLine(dateOrig + @" - " +dgvSummary.Rows[i].Cells[0].Value + @" - " + share);
                continue;
            }
            else
            {
                laborerOrig = dgvSummary.Rows[i].Cells[0].Value.ToString();
                dateOrig = dgvSummary.Rows[i].Cells[1].Value.ToString();
                if (laborerCopy == laborerOrig && dateCopy == dateOrig)
                {
                    share += Convert.ToDouble(dgvSummary.Rows[i].Cells[5].Value);
                    Console.WriteLine(dateOrig + @" - " + dgvSummary.Rows[i].Cells[0].Value + @" - " + share);
                }
                else if (laborerCopy == laborerOrig && dateCopy != dateOrig)
                {
                    dateCopy = dateOrig;
                    share = Convert.ToDouble(dgvSummary.Rows[i].Cells[5].Value);
                    Console.WriteLine(dateOrig + @" - " + dgvSummary.Rows[i].Cells[0].Value + @" - " + share);
                }
                else
                {
                    laborerCopy = laborerOrig;
                    dateCopy = dateOrig;
                    share = Convert.ToDouble(dgvSummary.Rows[i].Cells[5].Value);
                    Console.WriteLine(dateOrig + @" - " + dgvSummary.Rows[i].Cells[0].Value + @" - " + share);
                }

            }
        }
    }

This is different from the issue in "how I can show the sum of in a datagridview column?" because that example only sums everything in that column. What I need is to sum the values of the column with the same ID and the same date.

Your help is greatly appreciated.

That code is just a try. I don't know how to go about creating the for loop to accomplish what I need. — Ibanez1408, Jan 19 '17 at 05:51
This is different from the issue in "how I can show the sum of in a datagridview column?" because that example only sums everything in that column. What I need is to sum the values of the column with the same ID and the same date. — Ibanez1408, Jan 19 '17 at 06:12
I had this kind of problem before. While it is possible, I'd recommend creating multiple gridviews, one for each group (id in your case). This way you can easily add a row containing the sum of your objects and i think it has the potential to be more user friendly if done right — nozzleman, Jan 19 '17 at 06:25
If I create a gridview for each ID and Date, then It'll be thousands of datagridview. — Ibanez1408, Jan 19 '17 at 06:33

GantTheWanderer · Accepted Answer · 2017-01-19T08:35:57.367

2

Loop over the data once collecting like information into a dictionary.

Now the dictionary will contain lists of numbers per ID and Date that can be easily calculated.

private void GetTotalShareForDay()
{
    var results = new Dictionary<string,List<decimal>>();

    for (var i = 0; i < dgvSummary.Rows.Count; i++)
    {
        string laborerId = dgvSummary.Rows[i].Cells[0].Value.ToString();
        string date = dgvSummary.Rows[i].Cells[1].Value.ToString();

        string uniqueKey = laboredId + ","  + date; // Looking at your data, this should provide a unique ID

        decimal share = Convert.ToDecimal(dgvSummary.Rows[i].Cells[5].Value);

        if (!results.ContainsKey(uniqueKey)) 
            results.Add(uniqueKey, new List<decimal>());
        results[uniqueKey].Add(share);
    }

    foreach(var key in results.Keys)
    {
        // key is laborerID + "," + date
        var sum = results[key].Sum();
    }
}

Version with tuple instead (preferred)

private void GetTotalShareForDay()
{
    var results = new Dictionary<Tuple<string, string>,List<decimal>>();

    for (var i = 0; i < dgvSummary.Rows.Count; i++)
    {
        string laborerId = dgvSummary.Rows[i].Cells[0].Value.ToString();
        string date = dgvSummary.Rows[i].Cells[1].Value.ToString();

        Tuple<string, string> uniqueKey = new Tuple<string, string>(laborerID, date); // laborerID and date combined provide uniqueness

        decimal share = Convert.ToDecimal(dgvSummary.Rows[i].Cells[5].Value);

        if (!results.ContainsKey(uniqueKey)) 
            results.Add(uniqueKey, new List<double>());
        results[uniqueKey].Add(share);
    }

    foreach(var key in results.Keys)
    {
        // key.Item1 is laborerID
        // key.Item2 is Date as string
        decimal sum = results[key].Sum();
        Console.Write("Laborer ID:" + key.Item1);
        Console.Write("Date:" + key.Item2);
        Console.Write("Sum:" + sum);
    }
}

edited Jan 19 '17 at 08:35

answered Jan 19 '17 at 06:34

GantTheWanderer

1,255
1
11
19

We actually have the same idea but the way you code is way different from mine. Meaning, yours is a lot advanced than mine and I like it. I just couldn't make it work. "Tuple" I need to learn that. I have my code edit posted and it's working for me but if you can make it similar to what I have created, that is by using the Dictionary, tuple and such that'll be a great help to my advancement. – Ibanez1408 Jan 19 '17 at 06:56
Note that your algorithm works but assumes that like data is sequential. My code relies on the fact that combining the laborerid and date provides a unique identity that all amounts can be grouped by,thus order is not a factor in the algorithm. – GantTheWanderer Jan 19 '17 at 07:37
i made a string version of the code instead of using Tuple – GantTheWanderer Jan 19 '17 at 07:39
The sum of the first 3 rows should be 945.41 but with the code you gave me, the first sum is 1890.82 and the sum of the 2 rows after that is 537.03 but with your code gives me 1074.06. It is multiplying the "sum" to 2. – Ibanez1408 Jan 19 '17 at 07:59
Your solution is great only if we can get rid of the small bug of multiplying to 2. Probably I can just divide it by 2 to get the right answer but I would really want to find out why that error occurs. – Ibanez1408 Jan 19 '17 at 08:01
Is there more information in your grid than is shown in the example above? If not, you might check to see how long `results[key]` is which would be the final list of amounts. Does it contain more amounts than it should? – GantTheWanderer Jan 19 '17 at 08:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/133579/discussion-between-gantthewanderer-and-ibanez1408). – GantTheWanderer Jan 19 '17 at 18:50

Loop through datagridview and total amount of the same id

1 Answers1