Summing a column in a datagrid. How is this done? 
Author Message
 Summing a column in a datagrid. How is this done?

Assume I have a Windows (not web) form with "Order" as textbox fields and
"[Order Details]" as a datagrid.  Related as Parent/Child via dataset and
bound to the controls.  Also assume that the [Order Details] table has a
calculated column, like such:
..., Qty * Price * (1 - Discount) AS Extended, ...

I would like to show a textbox, for instance, directly under the order
details datagrid that would hold the current total of the Extended column.
I know there are many ways to accomplish this, but assume this form has
navigational controls on it.  The sum field must reflect the correct amount
when the record has moved.  OR, an item is added or deleted from the Order
Details table.

I realize this may be accomplished by perhaps using an SQL command, i.e.
SUM(extended) where [Order Details].OrderID = Order.OrderID, but wouldn't
that plow through the entire table to add, say... 4 measly fields?

Performance is the key here, not code.  I'd rather code for a week and have
a fast program, than code for a hour and have it working, but working
poorly.

And ideas or insight would be greatly appreciated.  Any .net code samples
that show this, specifically?



Tue, 01 Feb 2005 22:38:20 GMT  
 Summing a column in a datagrid. How is this done?
When you build the OrderHeader datatable, get the SUM for all the records.

For example,

SELECT OrderHeader.Id, OrderHeader.Number, OrderHeader.OrderDate,
SUM(OrderDetail.Qty) AS nTotalQty
   FROM OrderHeader, OrderDetail
 WHERE OrderHeader.Id = OrderDetail.OrderId
    GROUP BY OrderHeader.Id, OrderHeader.Number, OrderHeader.OrderDate

This will give you extended column for all records. Now when something is
changed in the detail section, you can calculate the SUM in the code. Just
create a  function like this.

private decimal GetSum(DataTable toDataTable, string tcField)
{
    decimal nTotal = 0;
    foreach(DataRow loRow in toDataTable.Rows)
    {
        nTotal = nTotal + loRow[tcField];
    }
    return nTotal;

Quote:
}

This way you won't have to hit server everytime something is changed.

Hope this helps.


Quote:
> Assume I have a Windows (not web) form with "Order" as textbox fields and
> "[Order Details]" as a datagrid.  Related as Parent/Child via dataset and
> bound to the controls.  Also assume that the [Order Details] table has a
> calculated column, like such:
> ..., Qty * Price * (1 - Discount) AS Extended, ...

> I would like to show a textbox, for instance, directly under the order
> details datagrid that would hold the current total of the Extended column.
> I know there are many ways to accomplish this, but assume this form has
> navigational controls on it.  The sum field must reflect the correct
amount
> when the record has moved.  OR, an item is added or deleted from the Order
> Details table.

> I realize this may be accomplished by perhaps using an SQL command, i.e.
> SUM(extended) where [Order Details].OrderID = Order.OrderID, but wouldn't
> that plow through the entire table to add, say... 4 measly fields?

> Performance is the key here, not code.  I'd rather code for a week and
have
> a fast program, than code for a hour and have it working, but working
> poorly.

> And ideas or insight would be greatly appreciated.  Any .net code samples
> that show this, specifically?



Tue, 01 Feb 2005 22:59:58 GMT  
 Summing a column in a datagrid. How is this done?
Use the Compute method of the DataTable object like this:

dim tblOrderDetails as DataTable = dstOrders.Tables(X)
dim decTotal as Decimal = tblOrderDetails.Compute("SUM(Extended)",
"OrderID=1020")

Substitute your own value for the OrderID (or whatever you've named that
column).

Quote:
> Assume I have a Windows (not web) form with "Order" as textbox fields and
> "[Order Details]" as a datagrid.  Related as Parent/Child via dataset and
> bound to the controls.  Also assume that the [Order Details] table has a
> calculated column, like such:
> ..., Qty * Price * (1 - Discount) AS Extended, ...

> I would like to show a textbox, for instance, directly under the order
> details datagrid that would hold the current total of the Extended column



Wed, 02 Feb 2005 01:08:46 GMT  
 Summing a column in a datagrid. How is this done?
Not to be a PITA, but could you go one step further and show me an example?
(or email me one).   Conceptually, I'm a little lost at why to total up the
quantity of the entire database?  Or, am I completely missing this?

Using the Northwind Traders database, how would you write a plain order
entry screen for a data entry person to take orders?  Showing fields like, a
subtotal field for the grid column, and a freight field, and a total order
field.


Quote:
> When you build the OrderHeader datatable, get the SUM for all the records.

> For example,

> SELECT OrderHeader.Id, OrderHeader.Number, OrderHeader.OrderDate,
> SUM(OrderDetail.Qty) AS nTotalQty
>    FROM OrderHeader, OrderDetail
>  WHERE OrderHeader.Id = OrderDetail.OrderId
>     GROUP BY OrderHeader.Id, OrderHeader.Number, OrderHeader.OrderDate

> This will give you extended column for all records. Now when something is
> changed in the detail section, you can calculate the SUM in the code. Just
> create a  function like this.

> private decimal GetSum(DataTable toDataTable, string tcField)
> {
>     decimal nTotal = 0;
>     foreach(DataRow loRow in toDataTable.Rows)
>     {
>         nTotal = nTotal + loRow[tcField];
>     }
>     return nTotal;
> }

> This way you won't have to hit server everytime something is changed.

> Hope this helps.



> > Assume I have a Windows (not web) form with "Order" as textbox fields
and
> > "[Order Details]" as a datagrid.  Related as Parent/Child via dataset
and
> > bound to the controls.  Also assume that the [Order Details] table has a
> > calculated column, like such:
> > ..., Qty * Price * (1 - Discount) AS Extended, ...

> > I would like to show a textbox, for instance, directly under the order
> > details datagrid that would hold the current total of the Extended
column.
> > I know there are many ways to accomplish this, but assume this form has
> > navigational controls on it.  The sum field must reflect the correct
> amount
> > when the record has moved.  OR, an item is added or deleted from the
Order
> > Details table.

> > I realize this may be accomplished by perhaps using an SQL command, i.e.
> > SUM(extended) where [Order Details].OrderID = Order.OrderID, but
wouldn't
> > that plow through the entire table to add, say... 4 measly fields?

> > Performance is the key here, not code.  I'd rather code for a week and
> have
> > a fast program, than code for a hour and have it working, but working
> > poorly.

> > And ideas or insight would be greatly appreciated.  Any .net code
samples
> > that show this, specifically?



Mon, 07 Feb 2005 21:09:41 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. AM I missing something - Sum and Cumulative Sum

2. sum of datagrid columns

3. Datagrid column sum

4. doing sums in a table outlook view

5. How to align column header differently from column in windows datagrid

6. Datagrid column reject input when added expression for parent columns

7. could not edit the column of datagrid where this column is being binded to an alias

8. Dynamically Added Datagrid Template Column - Checkbox.Checked state in Column Header

9. DataGrid: Tab from column to column???

10. Recordset: What am I doing wrong?

11. What am I doing wrong??

12. What am I doing wrong?

 

 
Powered by phpBB® Forum Software