logo
down
shadow

Dynamic DataTable Group by and Aggregate functions


Dynamic DataTable Group by and Aggregate functions

By : user2185067
Date : November 20 2020, 04:01 AM
Does that help I'm trying to apply SQL-Like group by on a datatable I have. However I can't simply use LINQ answers others have suggested, as I don't know columns I have before runtime - user selects them. However each column will either have an Aggregate function or be included in the group by. Imagine table like this one , You could use this approach:
code :
string columnToGroup = "XXX";  // user selected
DataTable aggrTable = new DataTable();
aggrTable.Columns.Add(columnToGroup);

var aggrColumns = tbl.Columns.Cast<DataColumn>()
    .Where(c => !c.ColumnName.Equals(columnToGroup, StringComparison.InvariantCultureIgnoreCase));

foreach (DataColumn col in aggrColumns)
    aggrTable.Columns.Add(col.ColumnName, typeof(int));

var grpQuery = tbl.AsEnumerable().GroupBy(r => r[columnToGroup]);
foreach (var grp in grpQuery)
{
    DataRow row = aggrTable.Rows.Add();
    row[columnToGroup] = grp.Key;
    foreach (DataColumn col in aggrColumns)
        row[col.ColumnName] = grp.Count(r => !r.IsNull(col.ColumnName));
}


Share : facebook icon twitter icon
Can we apply aggregate functions in the select clause (getting error: not a single-group group function)

Can we apply aggregate functions in the select clause (getting error: not a single-group group function)


By : Chandra Mouli Pandur
Date : March 29 2020, 07:55 AM
wish helps you ORA-00937: not a single-group group function
Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
code :
select LOSA_APP.app_ref_no AS "App.Ref.No.", 
   CODE_BRANCH.branch_name AS "Business Unit",
   ....
    sum(LOSA_FACILITIES.amt_appr) - DOCS_DISB_SIGN_OFF_LOG.sign_off_amt AS "Amount Not Disbursed",
   ...
 from
   losa_app LOSA_APP

 INNER JOIN
   code_branch CODE_BRANCH
 ON
   LOSA_APP.attend_branch = CODE_BRANCH.branch_id
   ...
 GROUP BY LOSA_APP.app_ref_no, LOSA_APP.app_ref_no
 HAVING  LOSA_APP.app_status='A'; -- Application Status in {‘accepted’} 
group by multiple columns WITHOUT using aggregate functions or group using cfoutput/cfloop?

group by multiple columns WITHOUT using aggregate functions or group using cfoutput/cfloop?


By : Aidan Rowe
Date : March 29 2020, 07:55 AM
like below fixes the issue You are actually pretty close with the group attribute of cfoutput. You just didn't finish what you started. I think you might want something like this.
code :
<cfoutput query="invall" group="dos">
    output data for this grouping
    <cfoutput group="company">
        output data for this grouping
        <cfoutput>
            output ungrouped data
        </cfoutput>
    </cfoutput>
</cfoutput>
How can I use sql Aggregate functions in C# DataTable

How can I use sql Aggregate functions in C# DataTable


By : Terry Collette
Date : March 29 2020, 07:55 AM
To fix the issue you can do I want to apply the follwing Sql query in my DataTable , something like:
code :
var query = from row in table.AsEnumerable()
            group row by row.Field<int>("MakeDistinct") into grp
            select new {AfterDistinct = grp.Key, Count = grp.Count()};

foreach(var row in query) {
    Console.WriteLine("{0}: {1}", row.AfterDistinct, row.Count);
}
Aggregate datatable with dynamic number of columns

Aggregate datatable with dynamic number of columns


By : Julian Koch
Date : March 29 2020, 07:55 AM
wish helps you I have a datatable with dynamic set of columns and want to aggregate the numeric based columns and keep the final rows into new datatable. , Here is a dynamic approach that should work for your requirement:
code :
var rows = table.AsEnumerable();
var columns = table.Columns.Cast<DataColumn>();
int i;  // used to check if a string column can be parsed to int
string columnToGroup = "partnername";
DataColumn colToGroup = columns.First(c => c.ColumnName.Equals(columnToGroup, StringComparison.OrdinalIgnoreCase));
var colsToSum = columns
     .Where(c => c != colToGroup &&
         (c.DataType == typeof(int) ||
         (c.DataType == typeof(string)
         && rows.All(r => int.TryParse(r.Field<string>(c), out i)))));
var columnsToSum = new HashSet<DataColumn>(colsToSum);

DataTable tblSum = table.Clone(); // empty table, same schema
foreach (var group in rows.GroupBy(r => r[colToGroup]))
{
    DataRow row = tblSum.Rows.Add();
    foreach(var col in columns)
    {
        if (columnsToSum.Contains(col))
        {
            int sum;
            if (col.DataType == typeof(int))
                sum = group.Sum(r => r.Field<int>(col));
            else
                sum = group.Sum(r => int.Parse(r.Field<string>(col)));
            row.SetField(col.ColumnName, sum);
        }
        else
            row[col.ColumnName] = group.First()[col];
    }
}
var table = new System.Data.DataTable();
table.Columns.Add("PartnerName", typeof(string));
table.Columns.Add("CreditCol", typeof(int));
table.Columns.Add("DebitCol", typeof(string));
table.Columns.Add("AmountCol", typeof(int));
table.Rows.Add("P1", 10, "20", 30);
table.Rows.Add("P2", 1, "2", 3);
table.Rows.Add("P3", 3, "1", 10);
table.Rows.Add("P2", 1, "100", 200);
PartnerName   CreditCol    DebitCol    AmountCol
P1            10             20         30
P2            2              102        203
P3            3              1          10
remove duplicate rows then aggregate into one row by group using datatable

remove duplicate rows then aggregate into one row by group using datatable


By : Lemon's
Date : March 29 2020, 07:55 AM
I wish this helpful for you I have duplicate rows in a data.table - see below. A couple of the variables are lists. , This is pretty messy, but:
code :
setDT(DT) # since the OP posted a data frame
DT[, lapply(.SD, function(x) unique(Filter(Negate(is.null), x)))]
#    SSN_TAX_ID L29 S14 S2 S7                 S71 S91
# 1:      12345  11   3  1  2  4.5, 8.0, 9.0,10.0 6,7
DT[, lapply(.SD, function(x) .(Reduce(union, x)))]
Related Posts Related Posts :
  • Replace "\\" with "\" in a path
  • CSS Not formatting inside repeater
  • CheckedListBox filter with a TextBox
  • How to not evaluate the third part of a ternary operator unless necessary
  • How to avoid going into cache block in c# and return to loop iteration?
  • How do I add just one element to an array when I call a method c#
  • Image.RotateFlip doesn't seem to rotate the Bitmap
  • swagger add document and operation for a generic controller
  • Avoid a random selection of the same point to imitate "human-like" behavior
  • Howto use PostgreSQL table names without double quotes in C# and Npgsql
  • .NET Core Entity Framework InvalidOperationException
  • Database Context save if valid
  • How do I flatten an array of arrays?
  • Serialize Property as Xml Attribute in new Element
  • How to make a flash-animation with a row of pictureboxes?
  • Add ClientId to the "Authorized API Clients"
  • Sending data between 2 forms
  • How do you get POSTed value from dynamically populated dropdownlist without the use of UpdatePanel?
  • C# Inconsistent accessibility: return type is less accessible than method
  • MVC Razor how to get option value from form and set viewmodel property to selected value
  • Automapper error after updating a record with POCO classes, Unity and EF6
  • Xamarin Forms Change NavBar background image on Specific pages
  • No service while customize a new User Context Identity
  • How to recognize element in which item in the gridview
  • Loading DataGridView only once based on listbox item selected
  • WCF Service not hitting from postman over https
  • Export telerik RadGrid to PDF
  • Why won't C# decimal ToString request culture AND currency?
  • Using constant string in all include pages
  • Write Roslyn analyzer by Rider
  • What is the difference between a dSYM folder and an mSYM folder?
  • replace inheritance with generic extension method
  • C# XAML binding
  • Span<T> and streams in .NET framework
  • Using phyton.exe in .net c# controller:
  • How to get distinct values from set of rows matching condition
  • MySqlParameter type conflict when using Entity Framework with Pomelo
  • Why there is black color shed on the upper side and left side on textbox border in ASP.Net
  • Autofac resolving named service incorrectly
  • Cumulative sum of array items
  • Bot connector returns Forbidden (403) with Microsoft.Bot.Connector.ErrorResponseException
  • What is the right way to show only group by column in datatable
  • C# Trying to get a list of questions from one class into my Main() class loop
  • In database AutoIncrease column. What if value reached maximum value
  • How to access controls of dynamically added user controls in a panel in C#?
  • Binding selected ListBox Image somewhere else in MainWindow
  • Apply Ninject to work with "child viewmodels" in my C# WPF application
  • Create System.Net.WebProxy from string including credentials
  • error in implementing Ignite.net with persistence
  • WPF Button click not properly comparing variable to array value
  • Create custom iterable Dictionary with one generic type
  • How do I use ExecuteScalar with a stored Procedure?
  • HttpPostedFileBase always returns null when posting it with Model but working perfact without model
  • WIndows 10 MIDL.exe
  • Different versions of ODAC causing error in C# application (local Vs Server)
  • Is it necessary to add [XamlCompilation(XamlCompilationOptions.Compile)] above every class in a Xamarin Forms App?
  • Creating Custom Picturebox with Draggable and Resizable Selection Window
  • Unable to connect to MongoDb (using authentication) using mongocsharpdriver 2.7.0
  • SSIS Script Task: Select from datatable1 into datatable2
  • reversing an array using methods and MessageBox.show
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org