Answered by:
Displaying the total of a column in a Web Part

Question
-
Hi,
I would like to return the total value of the Salary column in to a gridview.
Here's the contents of the gridview so far:
<asp:GridView ID="grid" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Title" HeaderText="Title" />
<asp:BoundField DataField="Birthday" HeaderText="Birthday" />
<asp:BoundField DataField="Male"HeaderText="Male" />
<asp:BoundField DataField="Salary" HeaderText="Salary" />
</Columns>
</asp:GridView>And here's the Code Behind
protected void Page_Load(object sender, EventArgs e)
{
SPWeb web = SPContext.Current.Web;
SPList list = web.Lists["Employee"];
SPListItemCollection items = list.Items;
grid.DataSource = items.GetDataTable();
grid.DataBind();
}While this displays all the details in the Employee list, I would just like to see the total of the Salary column. And to have it read something like:
Total Salary = (and the total here)
Thanks
Wednesday, July 11, 2012 7:04 AM
Answers
-
Hi,
In order to display the total of Salary column you can use LINQ on DataTable as follows:
SPWeb web = SPContext.Current.Web; SPList list = web.Lists["Employee"]; SPListItemCollection items = list.Items; // Fill DataTable with items inside SPList DataTable dataTable = items.GetDataTable(); // Use following expression if the type of "Salary" column in your Employee list is "Text" int total = dataTable.AsEnumerable().Where(x => !string.IsNullOrEmpty(x.Field<string>("Salary"))).Select(x => Convert.ToInt32(x.Field<string>("Salary"))).Sum(); // Use following expression if the type of "Salary" column in your Employee list is "Number" int sum = dataTable.AsEnumerable().Select(x => x.Field<int>("Salary")).Sum();
You can use the variable 'total' or 'sum' from above code snippet to display Total Salary.
The above code uses DataTableExtensions. So make sure you add a reference of 'System.Data.DataSetExtensions.dll' to your project to avoid compilation error.
Regards,
Devang Bhavsar- Marked as answer by zukecollins Tuesday, August 21, 2012 1:04 PM
Wednesday, July 11, 2012 6:41 PM -
Hi zukecollins,
Along with the above option, there is also one more OOTB alternative for achieving the same.
You can use "Totals" option inside the List Settings -> Views -> All Items -> Edit View. Just create/customize your existing view and select "Sum" from the dropdown box against the "Salary" column in Totals section.
Hope this helps.
Regards,
Devang Bhavsar- Marked as answer by zukecollins Tuesday, August 21, 2012 1:04 PM
Wednesday, July 11, 2012 7:08 PM
All replies
-
Hi,
In order to display the total of Salary column you can use LINQ on DataTable as follows:
SPWeb web = SPContext.Current.Web; SPList list = web.Lists["Employee"]; SPListItemCollection items = list.Items; // Fill DataTable with items inside SPList DataTable dataTable = items.GetDataTable(); // Use following expression if the type of "Salary" column in your Employee list is "Text" int total = dataTable.AsEnumerable().Where(x => !string.IsNullOrEmpty(x.Field<string>("Salary"))).Select(x => Convert.ToInt32(x.Field<string>("Salary"))).Sum(); // Use following expression if the type of "Salary" column in your Employee list is "Number" int sum = dataTable.AsEnumerable().Select(x => x.Field<int>("Salary")).Sum();
You can use the variable 'total' or 'sum' from above code snippet to display Total Salary.
The above code uses DataTableExtensions. So make sure you add a reference of 'System.Data.DataSetExtensions.dll' to your project to avoid compilation error.
Regards,
Devang Bhavsar- Marked as answer by zukecollins Tuesday, August 21, 2012 1:04 PM
Wednesday, July 11, 2012 6:41 PM -
Hi zukecollins,
Along with the above option, there is also one more OOTB alternative for achieving the same.
You can use "Totals" option inside the List Settings -> Views -> All Items -> Edit View. Just create/customize your existing view and select "Sum" from the dropdown box against the "Salary" column in Totals section.
Hope this helps.
Regards,
Devang Bhavsar- Marked as answer by zukecollins Tuesday, August 21, 2012 1:04 PM
Wednesday, July 11, 2012 7:08 PM