selecting totals in t-sql using subquery / adding totals in gridview footer

add two bounditems in gv and bind them to the fields selected from the sqldatasource with aggregate values
convert the fields into templatefields

make both fields visible=false

add this:

  Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.DataBound
        '------testing-------
        Dim tmp_GrandTotal_Total_GV As Label = CType(GridView1.FooterRow.FindControl("GV_LBL_GrandTotal_Total_Daily_Cost"), Label)
        Dim tmp_GT As Label = CType(GridView1.Rows(0).FindControl("label2"), Label)
        tmp_GrandTotal_Total_GV.Text = tmp_GT.Text
make sure you add the t-sql command as describled l
------------
This will not work for a paged gridview with more rows than the maximum per page. It can only show the total for the page. However, if the accumulator variable is only zeroed out in the
!Page.IsPostBack then the totals can accumulate but only based on the pages selected so any value could be created for the total depending on the pages selected. Therefore, there is no way to get a running total in a paged gridview.

add:

Text='<%# Bind("GrandTotal_Total_Daily_Cost") %>'
in the footertemplate
full listing
<asp:TemplateField HeaderText="Total_Daily_Cost" SortExpression="Total_Daily_Cost">
                <EditItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Eval("Total_Daily_Cost") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="GV_LBL_Total_Daily_Cost" runat="server" Text='<%# Bind("Total_Daily_Cost") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="GV_LBL_GrandTotal_Total_Daily_Cost" runat="server" Text='<%# Bind("GrandTotal_Total_Daily_Cost") %>'></asp:Label>
                </FooterTemplate>
            </asp:TemplateField>

use t-sql query to fetch the aggregate:

(SELECT SUM(ISNULL(Daily_Cost_Per_Person, 0) * ISNULL((ISNULL(No_of_Bene_Female, 0) + ISNULL(No_of_Bene_Male, 0)), 0)) As 'Total_Daily_Cost' FROM Data)
Full e.g:
SELECT
        CONVERT(VARCHAR, (CASE WHEN Autokey IS NULL THEN (SELECT TOP 1 '' AS Autokey FROM Data) ELSE Autokey END), 1) AS Autokey,
        CONVERT(VARCHAR,     (CASE     WHEN Province IS NULL THEN     (SELECT TOP 1 '' AS Province FROM Data)     ELSE Province    END), 1) AS Province,           
        CONVERT(VARCHAR,     (CASE     WHEN District IS NULL THEN     (SELECT TOP 1 '' AS District FROM Data)     ELSE District    END), 1) AS District,       
        CONVERT(VARCHAR,     (CASE     WHEN Hospital IS NULL THEN     (SELECT TOP 1 '' AS Hospital FROM Data)     ELSE Hospital    END), 1) AS Hospital,     
        CONVERT(VARCHAR,     (CASE     WHEN Caterer_Name IS NULL THEN     (SELECT TOP 1 '' AS Caterer_Name FROM Data)     ELSE Caterer_Name    END), 1) AS Caterer_Name,           
        CONVERT(CHAR(11),     (CASE     WHEN Date_of_Entry IS NULL THEN     (SELECT TOP 1 '' AS Date_of_Entry FROM Data)     ELSE Date_of_Entry    END), 106) AS Date_of_Entry,                   
        LEFT((CASE     WHEN Date_of_Entry = '01 Jan 1900' THEN     (SELECT TOP 1 '' AS Date_of_Entry FROM Data)     ELSE DATENAME(WEEKDAY, Date_of_Entry)    END), 3) AS Week,       
        CONVERT(VARCHAR,     (CASE     WHEN No_of_Daigs IS NULL THEN     (SELECT TOP 1 '' AS No_of_Daigs FROM Data)          ELSE No_of_Daigs    END), 1) AS No_of_Daigs,     
        CONVERT(VARCHAR,     (CASE     WHEN Food_Item IS NULL THEN     (SELECT TOP 1 '' AS Food_Item FROM Data)     ELSE Food_Item    END), 1) AS Food_Item,             
        CONVERT(VARCHAR,     (CASE     WHEN No_of_Rotis IS NULL THEN     (SELECT TOP 1 '' AS No_of_Rotis FROM Data)     ELSE No_of_Rotis    END), 1) AS No_of_Rotis,                     
        CONVERT(VARCHAR,     (CASE     WHEN No_of_Bene_Male IS NULL THEN     (SELECT TOP 1 '' AS No_of_Bene_Male FROM Data)     ELSE No_of_Bene_Male    END), 1) AS No_of_Bene_Male,                     
        CONVERT(VARCHAR,     (CASE     WHEN No_of_Bene_Female IS NULL THEN     (SELECT TOP 1 '' AS No_of_Bene_Female FROM Data)     ELSE No_of_Bene_Female    END), 1) AS No_of_Bene_Female,     
        CONVERT(VARCHAR,     (CASE     WHEN Daily_Cost_Per_Person IS NULL THEN     (SELECT TOP 1 '' AS Daily_Cost_Per_Person FROM Data)     ELSE Daily_Cost_Per_Person     END), 1) AS Daily_Cost_Per_Person,
        CONVERT(VARCHAR,     (CASE     WHEN Daily_Cost_Per_Person IS NULL THEN     (SELECT TOP 1 '' AS Daily_Cost_Per_Person FROM Data)     ELSE Daily_Cost_Per_Person     END), 1) AS Daily_Cost_Per_Person,
        ISNULL(No_of_Bene_Female, 0) + ISNULL(No_of_Bene_Male, 0) AS 'Total Bene',
        ISNULL(Daily_Cost_Per_Person, 0) * ISNULL((ISNULL(No_of_Bene_Female, 0) + ISNULL(No_of_Bene_Male, 0)), 0) As 'Total_Daily_Cost',
        (SELECT SUM(ISNULL(Daily_Cost_Per_Person, 0) * ISNULL((ISNULL(No_of_Bene_Female, 0) + ISNULL(No_of_Bene_Male, 0)), 0)) As 'Total_Daily_Cost' FROM Data)
        FROM Data

---
make sure to change forecolor of textbox to white if the background of page is black.