DATASET ROW SUM && COLUMN SUM

Sompal Arya

MY Dataset is given below in attachment......

i have try some many things but i did not get success ..

help me anybody..

i wanna do with datatable.compute method

Sir, Please check again for attachment

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
Well, the column totals are not too difficult. Assuming that the datatable is 'dt' and the column type is decimal in each case:

decimal billSum = (decimal)dt.Compute("Sum(billamount])", null);

decimal rebateSum = (decimal)dt.Compute("Sum(rebate)", null);

decimal outstandingSum = (decimal)dt.Compute("Sum(outstanding_amount)", null);

If you want to put these in the datatable instead, then you'll need to add a new row:

DataRow dr = dt.NewRow();
dr["billamount"] = billSum;
dr["rebate"] = rebateSum;
dr["outstanding_amount"] = outstandingSum;
dt.Rows.Add(dr);

If some of the other columns are primary keys or don't allow nulls, you'll have to put something in those as well.

However, you can't use DataTable.Compute to calculate row totals - you'll have to work them out manually.

If you want to put them in the datatable, then you'll need to add a new column first:

dt.Columns.Add("Total", typeof(decimal));

for(int i = 0; i < dt.Rows.Count; i++)
{
object o = dt.Rows[i]["billamount"];
decimal bill = !Convert.IsDBNull(o) ? (decimal)o : 0m;
object o2 = dt.Rows[i]["rebate"];
decimal rebate = !Convert.IsDBNull(o2) ? (decimal)o2 : 0m;
object o3 = dt.Rows[i]["outstanding_amount"];
decimal outstanding = !Convert.IsDBNull(o3) ? (decimal)o3 : 0m;
dt.Rows[i]["Total"] = bill + rebate + outstanding;
}

0
Sompal Arya12y ago
Thanks a lot for help
0
Vulpes12y ago
The aggregate functions: Sum, Count, Min, Max etc. only work on numeric columns.

So, if you're getting that error, it indicates that one or more of the columns is a string type (varchar etc in the database).

Can you tell me what the types of the columns are and I will adjust the code accordingly.

0
Sompal Arya12y ago
sir, i m getting error invalid use of aggergate function, please tell me what will i do?
0
Vulpes12y ago
Not sure what you're telling me here.

Are you saying that all these columns are strings rather than decimals and therefore need to be converted?
0
Sompal Arya12y ago
0
Sompal Arya12y ago
i want sum of billamount,rebate, outstanding_amount in each row's end
and i want sum of billamount,rebate, outstanding_amount in each Column's end.
0
Vulpes12y ago
OK, I can see from the attachment that you'll want to produce totals for the Bill Amount, Amount Paid, Rebate and Outstanding Amount columns.

However, as far as the rows are concerned, it looks like the arithmetic has been done already when working out the Outstanding Amount. Is there something else you want to calculate?
0
Sompal Arya12y ago
Sir,please check attachment again
0
Vulpes12y ago
The attachment is missing.

Can you remedy or just post the code.
0