DataTable compute with group by

Question

4.78/5 (2 votes)

See more:

Friends..

I need a help - here is my problem:
I have a Data Table and i am using the Compute method to find the sum
It looks like this:

C#

DataTable dTable = new DataTable();
        dTable.Columns.Add("AutoID");
        dTable.Columns.Add("Name");
        dTable.Columns.Add("Address");
        DataRow row = null;
        for (int i = 0; i < 5; i++)
        {
            row = dTable.NewRow();
            row["AutoID"] = i + 1;
            row["Name"] = i + " - Ram";
            row["Address"] = "Ram Nagar, India";
            dTable.Rows.Add(row);
        }
        dTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, USA");
        dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA");

        double sum = Convert.ToDouble(dTable.Compute("SUM(AutoID)", ""));


//Upto here its fine...

        object sum1 = dTable.Compute("SUM(AutoID)", "group by Address");
 //this makes error like 
//Syntax error: Missing operand after 'by' operator.

Any help.....

Posted 21-Sep-10 21:13pm

AshiqueAhammed

Updated 26-Feb-16 2:29am

Sandeep Mewara

v2

Add a Solution

3 solutions

Solution 1

Try wrapping Address in single quotes - "group by " + "'" "Address" + "'".

In a sql query Adress should be in a single quote - 'Address'

Posted 21-Sep-10 21:28pm

anshudutta

Solution 3

Do you want to get a table with

object sum1 = dTable.Compute("SUM(AutoID)", "group by Address");

?

It's wrong, Compute return a single value.

and the second parameter is the filter
so for select SUM(AutoId) from mytable where address = 'Ram Nagar, India'
do

object sum1 = dTable.Compute("SUM(AutoID)", "Address = 'Ram Nagar, India'");

you can use all SQL boolean command such as LIKE, <, >, <>

Posted 20-Nov-14 8:10am

Yves Peter

Updated 20-Nov-14 8:13am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hiren solanki · Accepted Answer · 2010-09-21T22:57:00

The data table itself will not provide you with group by operation some extent it will be better if you use LINQ on Data table to accomplish your solution.

Look at this Sample.

C#

DataTable dTable = new DataTable();
        dTable.Columns.Add("id",typeof(int));
        dTable.Columns.Add("name",typeof(string));
        dTable.Rows.Add(1, "hiren");
        dTable.Rows.Add(2, "solanki");
        dTable.Rows.Add(3, "hiren");
        var query = from row in dTable.AsEnumerable()
                group row by row.Field<string>("name") into grp
                select new
                {
                    Id = grp.Key,
                    sum = grp.Sum(r=>r.Field<int>("id"))
                };
        foreach (var grp in query)
        {
            Response.Write(String.Format("The Sum of '{0}' is {1}", grp.Id, grp.sum));
        }

Please Vote or Mark as A answer if Solved.