How to calculate totals of column data?

Question

I am filling GridView from store procedure which returns this to me.

but problem is in last row, you can see that it returns me sum of TotalTransactions column but what i want is sum of totals only i.e. encircled elements, and it grows on

<asp:GridView ID="GridViewConductorTransactions" runat="server" Width="100%" AutoGenerateColumns="False"
              ShowFooter="True" OnRowDataBound="GridViewConductorTransactions_RowDataBound" CssClass="table table-hover table-striped table-bordered">
    <Columns>
        <asp:TemplateField HeaderText="Conductor Name" HeaderStyle-CssClass="visible-desktop" ItemStyle-CssClass="visible-desktop">
            <ItemTemplate>
                <asp:Label ID="lblConductorName" runat="server" Text='<%# Eval("ConductorName") %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:Label ID="totals" runat="server" Text="Totals"></asp:Label>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Denominations" HeaderStyle-CssClass="visible-desktop" ItemStyle-CssClass="visible-desktop">
            <ItemTemplate>
                <asp:Label ID="lblDenomination" runat="server" Text='<%# Eval("Denomination") == DBNull.Value ? "Grand Total" : Eval("Denomination") %>'></asp:Label>
            </ItemTemplate>
            <%--<FooterTemplate>
                <asp:Label runat="server" ID="lblTotalDenominations" Font-Bold="true"></asp:Label>
                </FooterTemplate> --%>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Total Transactions" HeaderStyle-CssClass="visible-desktop" ItemStyle-CssClass="visible-desktop">
            <ItemTemplate>
                <asp:Label ID="lblTotalTransactions" runat="server" Text='<%# Bind("totaltransactions") %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:Label runat="server" ID="lblTotalTransactionsSum" Font-Bold="true"></asp:Label>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Total Amount" HeaderStyle-CssClass="visible-desktop" ItemStyle-CssClass="visible-desktop">
            <ItemTemplate>
                <asp:Label ID="lblTotalAmount" runat="server" Text='<%# Bind("totalamount") %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:Label runat="server" ID="lblTotalAmountSum" Font-Bold="true"></asp:Label>
            </FooterTemplate>
        </asp:TemplateField>

    </Columns>
</asp:GridView>

protected void GridViewConductorTransactions_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {

        string lblTotalTransactions = ((Label)e.Row.FindControl("lblTotalTransactions")).Text;
        TotalTransactionsSum = TotalTransactionsSum + float.Parse(lblTotalTransactions);

        string lblTotalAmount = ((Label)e.Row.FindControl("lblTotalAmount")).Text;
        TotalAmountSum = TotalAmountSum + float.Parse(lblTotalAmount);

    }

    if (e.Row.RowType == DataControlRowType.Footer)
    {

        Label lb5 = (Label)(e.Row.FindControl("lblTotalTransactionsSum"));
        lb5.Text = TotalTransactionsSum.ToString("n2");

        Label lb7 = (Label)(e.Row.FindControl("lblTotalAmountSum"));
        lb7.Text = TotalAmountSum.ToString("n2");
    }
}

SP:

SELECT N, 
       case when N = 1 then ConductorName else NULL end ConductorName, 
       Denomination, 
       totaltransactions, 
       totalamount
    FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY c.name order by c.name) 'N',
        CASE WHEN isnull(CAST(T.amount AS varchar(30)), c.name + ' total') LIKE '%total%' 
             THEN NULL
             ELSE c.name 
        END AS ConductorName, 
        ISNULL(CAST(T.Amount AS varchar(30)), c.Name + ' total') AS Denomination, 
        COUNT(*) AS totaltransactions, 
        SUM(T.Amount) AS totalamount
    FROM dbo.Tickets AS T INNER JOIN
            Transport.Conductors AS c ON c.ConductorID = T.Conductor_ID
    WHERE CONVERT(DATE,ServerDateTime) BETWEEN @FromDate and @ToDate
    GROUP BY c.Name, T.Amount WITH ROLLUP

Answer 1

I would adjust the data in the Query itself. But you haven't provided any details on your data. So Other way is use theGridViewConductorTransactions_RowDataBound method to get what you want. You can check the if row contains the total in txt then only add the sum to the variable as,

  if (e.Row.RowType == DataControlRowType.DataRow)
    {
          string denominationText = ((Label)e.Row.FindControl("lblDenomination")).Text;
          if(denominationText.Contains("total"))
          {
             string lblTotalAmount = ((Label)e.Row.FindControl("lblTotalAmount")).Text;
             TotalAmountSum = TotalAmountSum + float.Parse(lblTotalAmount);    

             string lblTotalTransactions = ((Label)e.Row.FindControl("lblTotalTransactions")).Text;
             TotalTransactionsSum = TotalTransactionsSum + float.Parse(lblTotalTransactions);
          }

    }