How can I get a sum for the column "pieces" in a datatable?

Question

How can I get a sum for the column "pieces" in a datatable? Say I had the following table. How can I calculate the "total" pieces for article="milk" and artno="15"?

Columns:   article     artno    pieces
Rows:
1          milk        15       1
2          water       12       1
3          apple       13       2
4          milk        15       1
5          milk        16       1
6          bread       11       2
7          milk        16       4

The Result of the my new DataTable should be this:

Columns:   article     artno    pieces
Rows:
1          bread       11       2
2          water       12       1
3          apple       13       2
4          milk        15       2
5          milk        16       5

My Code:

foreach (DataRow foundDataRow in foundRows1)
{
    int i = 0;
    foreach (DataRow dataRow in foundRows2)
    {
        if (object.Equals(dataRow.ItemArray[0], foundDataRow.ItemArray[0])
            && object.Equals(dataRow.ItemArray[3], foundDataRow.ItemArray[3]))
        {
            i = i + Convert.ToInt16(dataRow.ItemArray[4]);
        }
    }
    Debug.Print(i.ToString());
}

Sorry, but i'm new DataBase developer and my english speak language is not so good.

Solution 1

Using the DataTable.Compute method, you can do:

int sum = (int)table.Compute("Sum(pieces)", "article = 'milk' AND artno='15'");

Solution 2

You could use the AsEnumerable method like so:

var results = dataTable.AsEnumerable()
                       .Where(row => row.Field<string>("article") == "milk" && 
                                     row.Field<int>("artno") == 15)
                       .Select(row => row.Field<int>("pieces"))
                       .Sum();

Solution 3

Using Michael's suggestion with 1 less step:

var results = dataTable.AsEnumerable()
                       .Where(row => (row.Field<string>("article ") == "milk") &&
                                     (row.Field<int>("artno") == 15))
                       .Sum(row => row.Field<int>("pieces"));

(This is using Linq to DataSet)

Comments

[McSteel]

Thx for answering. But the column "article" and "artno" are not even the same data. Everytime there are other datas.

[McSteel]

No that isn't 1 to 1 relation, because milk can be artno = 15 or 16

[vc 74]

@McSteel I think there was a little delay between my comment and answer correction... OK now?

[McSteel]

dt.Compute("SUM(pieces)", string.Empty) can i use for Sum of all pieces

[Davide Piras]

should still work the Compute function is there since .NET 1.x

[Davide Piras]

what are you asking? can you try my code and see how it works and call Compute on your table and check the result? I think this answers your question.

[McSteel]

my datatable have not an AsEnumerable() Function?!

[Michael Minton]

You may need to add a reference to System.Data.