DatagridView C# Sum of columns

Question

Hello I'm wondering how can I get the sum of a column according with the value in another column. For example I have a DatagridView like this

User   Value to sum
-------------------
User1       10
-------------------
User1       15
-------------------
User2       20
-------------------
User3       30

I want to know how can I get the sum for each user.

Expected output

User1: 25        
User2: 20     
User3: 30

So far I have the code for get the sum of all users but I don't know hot to get per user.

Thanks in advance for everyone.

for (int i = 0; i < dataGridView2.Rows.Count-1; i++)
{
    total += Convert.ToInt32(dataGridView2.Rows[i].Cells[ColumnN].Value);
}

Instead of using the `DataGridView` as source, you can't use the source of the `DataGridView`? That's easier — Tim Schmelter, May 31 '18 at 14:15
Maybe if you compute the values in the query, you can group by user and use sum to compute the value. — Juan, May 31 '18 at 14:24
The data source is an excel file that I loaded as DataTable in order to populate the DatagridView I don't know if there is the best way to do it, but it was the only one that I thought, How @Juan How can I do that? — Shepniel Sh, May 31 '18 at 14:38
Maybe you can load the data from excel, store in sql server(or any database system you have) do the query to compute the values and the read the result of the query and present it in the grid. — Juan, May 31 '18 at 14:40
Maybe this method can help you, but sometimes fails if the format is not correct. https://stackoverflow.com/questions/24150739/code-to-read-xlsx-sheet-into-a-table-in-a-sql-server-database If you write a query like the one in the question you can do combine it with the group by and the sum to feed the grid or combine it with an insert to load the data to sql server. — Juan, May 31 '18 at 14:44

Tim Schmelter · Accepted Answer · 2018-05-31T14:53:48.860

1

You could use this query:

var userSums = dataGridView2.Rows.Cast<DataGridViewRow>()
    .GroupBy(row => row.Cells[0].Value.ToString())
    .Select(g => new { User = g.Key, Sum = g.Sum(row => Convert.ToInt32(row.Cells[1].Value)) });

edited May 31 '18 at 14:53

answered May 31 '18 at 14:20

Tim Schmelter

450,073
74
686
939

This just works like charm. Thank you very much Tim! – Shepniel Sh May 31 '18 at 14:39

score 0 · Answer 2 · answered May 31 '18 at 14:39

If you extract your data as a list of pairs you can use the codes below:

        var data = new List <KeyValuePair<string, int>>()
        {
            new KeyValuePair<string, int>( "User1", 10 ),
            new KeyValuePair<string, int>( "User1", 15 ),
            new KeyValuePair<string, int>( "User2", 20 ),
            new KeyValuePair<string, int>( "User3", 30 )
        };

        var res = data.GroupBy(x => x.Key);

        foreach (var item in res)
        {
            var user = item.ToList();
            var sum = item.Sum(x => x.Value);
            Console.WriteLine(user[0].Key + " " + sum);
        }

DatagridView C# Sum of columns

2 Answers2

Linked