How to get summary from Gridview Output.

Posted on 2012-08-31
Last Modified: 2012-09-17
I have the Gridview where data is populated like this.

Source      State      Count
CI                 A                5
CI                 B                5
CI                 C                5
CI                 D                5
CI                 E                5
CM                 F                3
CM                G                3
CM                H                3

How to get the summary Below as
CI Total - 25, CM Total - 9
Question by:searchsanjaysharma
    LVL 18

    Assisted Solution

    by:Rajar Ahmed
    Try this,
    Add this on markup of your gridview
    ShowFooter="true" OnRowDataBound="gvData_RowDataBound" 

    Open in new window

    //have these variables on your codebehind
    Int32 count = 0;
        StringBuilder footerText = new StringBuilder();
        string compSource = "";
    protected void gvData_RowDataBound(object sender, GridViewRowEventArgs e)
            if (e.Row.RowType == DataControlRowType.DataRow)
                if (compSource != e.Row.Cells[0].Text)
                    if (count > 0)
                        footerText.Append(" , " + e.Row.Cells[0].Text + " Total - ");
                        count = 0;
                    else if (string.IsNullOrEmpty(compSource))
                        footerText.Append(e.Row.Cells[0].Text + " Total - ");
                    compSource = e.Row.Cells[0].Text;
                compSource = e.Row.Cells[0].Text;
                count += Int32.Parse(e.Row.Cells[2].Text);            
            else if (e.Row.RowType == DataControlRowType.Footer){
                e.Row.Cells[0].Text = footerText.ToString();

    Open in new window

    LVL 26

    Accepted Solution

    Hi searchsanjaysharma
    Could do it with a datalist populated by a stored procedure, totalling COUNT values can't be done with inline SQL, but it can be done.
    Totalling SQL COUNT results with a Datalist
    Codeless databinding Gridview and Datalist to SqlDataSources
    <%@ Page Language="VB" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
    <script runat="server">
    <html xmlns="">
    <head runat="server">
        <form id="form1" runat="server">
          <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
              <asp:BoundField DataField="Source" HeaderText="Source" 
                SortExpression="Source" />
              <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
              <asp:BoundField DataField="Count" HeaderText="Count" ReadOnly="True" 
                SortExpression="Count" />
          <asp:SqlDataSource ID="CountsSqlDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:cnSessionTest %>" 
            SelectCommand="Table_2_Counts_Sel" SelectCommandType="StoredProcedure"></asp:SqlDataSource>  
          <asp:DataList ID="TotalsDataList" runat="server" 
            DataSourceID="TotalsSqlDataSource" RepeatDirection="Horizontal" 
            RepeatLayout="Flow" >
              <asp:Label ID="SourceLabel" runat="server" Text='<%# Eval("Source") %>' /> Total - <asp:Label ID="TotalsLabel" runat="server" Text='<%# Eval("Totals") %>' />
              <ItemStyle Wrap="False" />
          <asp:SqlDataSource ID="TotalsSqlDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:cnSessionTest %>" 
            SelectCommand="Table_2_Totals_Sel" SelectCommandType="StoredProcedure"></asp:SqlDataSource>  

    Open in new window

    And the Stored procedures, which you would need to modify for correct table names.
    ALTER PROCEDURE [dbo].[Table_2_Counts_Sel]
    SELECT	 t1.Source
    		,(	SELECT     COUNT(Source) AS 'Count'
    			FROM          dbo.Table_2 AS t2
    			WHERE      (Source = t1.Source)) AS 'Count' 
    FROM         sessiontest.dbo.Table_2 AS t1
    GROUP BY t1.Source, t1.[State]
    -- Testing
    -- exec Table_2_Counts_Sel
    -- Output
    -- CI	A	5
    -- CI	B	5
    -- CI	C	5
    -- CI	D	5
    -- CI	E	5
    -- CM	F	3
    -- CM	G	3
    -- CM	H	3

    Open in new window

    ALTER PROCEDURE [dbo].[Table_2_Totals_Sel]
    -- Create a temp table TO store the select results
    CREATE TABLE #SumCountSource
        [Source] [varchar](50) NULL,
        [State] [varchar](50) NULL,
        [CountOfSource] INT NULL
    INSERT INTO #SumCountSource
    SELECT	 t1.Source
    		,(	SELECT     COUNT(Source) AS 'Count'
    			FROM          dbo.Table_2 AS t2
    			WHERE      (Source = t1.Source)) AS 'CountOfSource' 
    FROM         sessiontest.dbo.Table_2 AS t1
    GROUP BY t1.Source, t1.[State]
    SELECT Source, SUM([CountOfSource]) AS Totals
    FROM   #SumCountSource
    GROUP BY [Source]
    DROP TABLE #SumCountSource
    -- Testing
    -- exec Table_2_Totals_Sel
    -- Output
    -- CI	25
    -- CM	9

    Open in new window

    Alan ";0)

    Author Closing Comment


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now