locked
Displaying the total of a column in a Web Part RRS feed

  • 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. 

    Display Totals in List Views

    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. 

    Display Totals in List Views

    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