Wednesday, August 18, 2010

Apply group by clause on Datatable in C#

The blog will explain how to retrieve a data table using C# using a group by clause.
//The way to call the Group By Function
//Argument 1 = Data table in IMAGE 1
//Argument 2 = The fields you want for the data table returned
//Argument 3 = The field you want to group by
//Argument 4 = The function you want to do It can be either SUM, COUNT, AVG etc.
DataTable dtGroupedBy = GetGroupedBy(dt, "CodeName,Quantity,Current", "CodeName", "Sum");
/// <summary>
/// The group by clause for Data Table
/// </summary>
/// <param name="dt">The main data table to be filtered with the Group By.</param>
/// <param name="columnNamesInDt">The Fields sent seperated by commas. EG "Field1,Field2,Field3"</param>
/// <param name="groupByColumnNames">The column name which should be used for group by.</param>
/// <param name="typeOfCalculation">The calculation type like Sum, Avg Or Count.</param>
/// <returns></returns>
private DataTable GetGroupedBy(DataTable dt, string columnNamesInDt, string groupByColumnNames, string typeOfCalculation)
{
    //Return its own if the column names are empty
    if (columnNamesInDt == string.Empty || groupByColumnNames == string.Empty)
    {
        return dt;
    }

    //Once the columns are added find the distinct rows and group it bu the numbet
    DataTable _dt = dt.DefaultView.ToTable(true, groupByColumnNames);

    //The column names in data table
    string[] _columnNamesInDt = columnNamesInDt.Split(',');

    for (int i = 0; i < _columnNamesInDt.Length; i = i + 1)
    {
        if (_columnNamesInDt[i] != groupByColumnNames)
        {
            _dt.Columns.Add(_columnNamesInDt[i]);
        }
    }


    //Gets the collection and send it back
    for (int i = 0; i < _dt.Rows.Count; i = i + 1)
    {
        for (int j = 0; j < _columnNamesInDt.Length; j = j + 1)
        {
            if (_columnNamesInDt[j] != groupByColumnNames)
            {
                _dt.Rows[i][j] = dt.Compute(typeOfCalculation + "(" + _columnNamesInDt[j] + ")", groupByColumnNames + " = '" + _dt.Rows[i][groupByColumnNames].ToString() + "'");
            }
        }
    }

    return _dt;
}


Screen shots to show the achievements


Image 1 :

Image 2 :

Related Blog

Apply "Multiple" column Group By on DataTable in C#

19 comments:

  1. can i fix this one.
    Invalid usage of aggregate function Sum() and Type: String.

    ReplyDelete
    Replies
    1. Pradeep, I think you are trying to use the Sum() on a string datatype returning from SQL. Convert it to Int or Decimal and try again. I am sure it will work. This is a 100% working example. So i am sure that will work. Best of Luck.

      Delete
    2. Thanks!

      Can you help me to group by with multiple columns?

      Delete
  2. Could you please share the code to support multiple "group by"?

    Thanks in advance!

    ReplyDelete
    Replies
    1. I think you should be able to add comma seperated column names . You will have to change code a bit just to see the column is included in the column which we are showing.

      Delete
  3. I would like to see the code for grouping across multiple columns also.

    ReplyDelete
    Replies
    1. @Anonymous - I will create a demo and get the link up.

      Delete
    2. Here you go try this. http://maulikdhorajia.blogspot.com/2012/05/apply-multiple-column-group-by-on.html

      Delete
  4. Excelent Work, many thanks :)

    ReplyDelete
  5. Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.

    ReplyDelete
    Replies
    1. @Sukumar.R,

      Please visit the other blog. This should help you out.

      http://maulikdhorajia.blogspot.com/2012/05/apply-multiple-column-group-by-on.html

      Thanks,
      Maulik Dhorajia

      Delete
  6. This code was not helpful for me....I am unable to group similar data from a column....

    ReplyDelete
  7. thanks...works perfectly

    ReplyDelete