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

Goal

A few days ago a reader asked for Multiple column group by on DataTable. This blos is in reply of that question. This was my old blog Apply group by clause on Datatable in C#.

Solution

#region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
#endregion

namespace SampleApplication
{
    #region Enums

    /// <summary>
    /// The functions which can be used for aggreation
    /// </summary>
    public enum AggregateFunction
    {
        Sum,
        Avg,
        Count,
        Max,
        Min
    }

    #endregion

    #region Entity

    /// <summary>
    /// The class which will have properties of function to be performed and on which field
    /// </summary>
    public class DataTableAggregateFunction
    {
        /// <summary>
        /// The function to be performed
        /// </summary>
        public AggregateFunction enmFunction { get; set; }

        /// <summary>
        /// Performed for which column
        /// </summary>
        public string ColumnName { get; set; }

        /// <summary>
        /// What should be the name after output
        /// </summary>
        public string OutPutColumnName { get; set; }
    }

    #endregion
    

    public class Helper
    {
        /// <summary>
        /// Demo for Group By
        /// </summary>
        public void DemoGroupBy()
        { 
            //Gets the mock data table
            DataTable _dt = GetDataTable();

            //Add columns which you want to group by
            IList<string> _groupByColumnNames = new List<string>();
            _groupByColumnNames.Add("State");
            _groupByColumnNames.Add("City");

            //Functions you want to perform on which fields
            IList<DataTableAggregateFunction> _fieldsForCalculation = new List<DataTableAggregateFunction>();
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Avg, ColumnName = "Population", OutPutColumnName = "PopulationAvg" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Sum, ColumnName = "Population", OutPutColumnName = "PopulationSum" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Count, ColumnName = "Population", OutPutColumnName = "PopulationCount" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Max, ColumnName = "Year", OutPutColumnName = "YearMax" });
            _fieldsForCalculation.Add(new DataTableAggregateFunction() { enmFunction = AggregateFunction.Min, ColumnName = "Year", OutPutColumnName = "YearMin" });

            //Gets the result after grouping by
            DataTable dtGroupedBy = GetGroupedBy(_dt, _groupByColumnNames, _fieldsForCalculation);
        }


        /// <summary>
        /// Returns a mock data table
        /// </summary>
        /// <returns></returns>
        private DataTable GetDataTable()
        {
            //Declarations
            DataTable _dt = new DataTable();
            DataRow _dr;
            
            //Create columns
            _dt.Columns.Add(new DataColumn() { ColumnName = "State" });
            _dt.Columns.Add(new DataColumn() { ColumnName = "City" });
            _dt.Columns.Add(new DataColumn() { ColumnName = "Year", DataType = typeof(System.Int32) });
            _dt.Columns.Add(new DataColumn() { ColumnName = "Population", DataType = typeof(System.Int32) });

            //Add mock data
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Ahmedabad"; _dr["Year"] = 2009; _dr["Population"] = 6000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Surat"; _dr["Year"] = 2009; _dr["Population"] = 2000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Rajkot"; _dr["Year"] = 2009; _dr["Population"] = 1000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Mumbai"; _dr["Year"] = 2009; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Pune"; _dr["Year"] = 2009; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);

            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Ahmedabad"; _dr["Year"] = 2010; _dr["Population"] = 8000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Mumbai"; _dr["Year"] = 2010; _dr["Population"] = 8000000; _dt.Rows.Add(_dr);

            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Ahmedabad"; _dr["Year"] = 2011; _dr["Population"] = 6000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Surat"; _dr["Year"] = 2011; _dr["Population"] = 2000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Gujarat"; _dr["City"] = "Rajkot"; _dr["Year"] = 2011; _dr["Population"] = 1000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Mumbai"; _dr["Year"] = 2011; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);
            _dr = _dt.NewRow(); _dr["State"] = "Maharastra"; _dr["City"] = "Pune"; _dr["Year"] = 2011; _dr["Population"] = 3000000; _dt.Rows.Add(_dr);

            //Return table
            return _dt;
        }

        /// <summary>
        /// Group by DataTable
        /// </summary>
        /// <param name="_dtSource"></param>
        /// <param name="_groupByColumnNames"></param>
        /// <param name="_fieldsForCalculation"></param>
        /// <returns></returns>
        private DataTable GetGroupedBy(DataTable _dtSource, IList<string> _groupByColumnNames, IList<DataTableAggregateFunction> _fieldsForCalculation)
        {
            
            //Once the columns are added find the distinct rows and group it bu the numbet
            DataTable _dtReturn = _dtSource.DefaultView.ToTable(true, _groupByColumnNames.ToArray());

            //The column names in data table
            foreach (DataTableAggregateFunction _calculatedField in _fieldsForCalculation)
            {
                _dtReturn.Columns.Add(_calculatedField.OutPutColumnName);
            }

            //Gets the collection and send it back
            for (int i = 0; i < _dtReturn.Rows.Count; i = i + 1)
            {
                #region Gets the filter string
                string _filterString = string.Empty;
                for (int j = 0; j < _groupByColumnNames.Count; j = j + 1)
                {
                    if (j > 0)
                    {
                        _filterString += " AND ";
                    }
                    if (_dtReturn.Columns[_groupByColumnNames[j]].DataType == typeof(System.Int32))
                    {
                        _filterString += _groupByColumnNames[j] + " = " + _dtReturn.Rows[i][_groupByColumnNames[j]].ToString() + "";
                    }
                    else
                    {
                        _filterString += _groupByColumnNames[j] + " = '" + _dtReturn.Rows[i][_groupByColumnNames[j]].ToString() + "'";
                    }
                }
                #endregion

                #region Compute the aggregate command

                foreach (DataTableAggregateFunction _calculatedField in _fieldsForCalculation)
                {
                    _dtReturn.Rows[i][_calculatedField.OutPutColumnName] = _dtSource.Compute(_calculatedField.enmFunction.ToString() + "(" + _calculatedField.ColumnName + ")", _filterString);
                }
                
                #endregion
            }

            return _dtReturn;
        }
    }
}

How to Use?

Call the function from somewhere.

(new SampleApplication.Helper()).DemoGroupBy();

Screenshot

Before Group By :

After Group By :

Please let me know if this helped.

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#