• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

How to get summary from Gridview Output.

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
2 Solutions
Rajar AhmedConsultantCommented:
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

Alan WarrenApplications DeveloperCommented:
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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">


<html xmlns="http://www.w3.org/1999/xhtml">
<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)
searchsanjaysharmaAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now