ASP.NET Web Forms GridView and SqlDataSource - how to divide the Sum of an Count?

Question

I want to divide an SUM of SqlDataSource with the COUNT of an other SqlDataSource, in an GridView?

How is that Possible?

Answer 1

Well, you can always do any kind of calculation for a given row with the RowDataBind event. This event runs once for each row rendered, and hence you are free to do any calculations you want.

Say I have a GridView of Hotels, and the room night rate.

For each row of the GridView, then I fill with a list of hotels, but also display the average room rate for that given city.

Hence, we could query the data with this query:

        "SELECT City, count(*) as Count, sum(NightRate) as rSum, 
        AVG(NightRate) as rAvg from tblhotelsA
        GROUP BY City"

And the result is this:

City	Count	rSum	rAvg
Banff	3	381.00	127.00
Calgary	1	137.00	137.00
Canmore	1	143.00	143.00
Edmonton	11	1398.00	127.0909
Fernie	1	143.00	143.00
Golden	3	414.00	138.00
Invermere	1	164.00	164.00
Kamloops	1	137.00	137.00
Kelowna	1	183.00	183.00
Penticton	1	114.00	114.00
Toronto	2	385.00	192.50
Vernon	1	209.00	209.00

So, then say this GridView markup:

        <asp:GridView ID="GVHotels" runat="server" AutoGenerateColumns="False"
            DataKeyNames="ID" CssClass="table table-hover"
            Width="50%"
            OnRowDataBound="GVHotels_RowDataBound"
            >
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="HotelName" HeaderText="Hotel" />
                <asp:BoundField DataField="Description" HeaderText="Description" />
                <asp:BoundField DataField="APrice" HeaderText="Average Price"
                    DataFormatString="{0:n}"/>
            </Columns>
        </asp:GridView>

And code behind is this:

Dim rstAverageCityPrice As New DataTable

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadData()      ' first time grid load
    End If

End Sub

Sub LoadData()

    Dim rstData As DataTable
    Dim strSQL As String =
        "SELECT *FROM tblHotelsA 
        ORDER BY HotelName"

    rstData = MyRst(strSQL)
    rstData.Columns.Add("APrice", GetType(Decimal))


    strSQL =
        "SELECT City, count(*) as Count, sum(NightRate) as rSum, 
        AVG(NightRate) as rAvg from tblhotelsA
        GROUP BY City"

    rstAverageCityPrice = MyRst(strSQL)

    GVHotels.DataSource = rstData
    GVHotels.DataBind()


End Sub

Protected Sub GVHotels_RowDataBound(sender As Object, e As GridViewRowEventArgs)

    If e.Row.RowType = DataControlRowType.DataRow Then

        Dim ThisRowData As DataRowView = e.Row.DataItem
        Dim sCity = ThisRowData("City")

        Dim CityAvg As DataRow() = rstAverageCityPrice.Select($"[City] = '{sCity}'")

        If CityAvg.Length > 0 Then
            e.Row.Cells(5).Text = CType(CityAvg(0)("rAvg"), Decimal).ToString("F2")
        End If

    End If

End Sub

And the result is this:

So, note how the average price is the result of 2 separate SQL queries. And we find/pull the average price based on the city for that given row.

As noted, you are free to thus do any kind of calculation, and add such columns to the GridView.

Hence, you can sum one data source, count another, and freely place the results into another column in the GridView using the above approach.

However, in most cases, you are better off to use one data source, and use a SQL sub query. In place of all the above extra code, I could just use one query and a sub query like this:

SELECT *, 
(SELECT avg(NightRate) FROM tblHotelsA as avgTable 
WHERE avgTable.City = tblHotelsA.City
GROUP BY avgTable.City)
 as APrice
FROM tblHotelsA
ORDER BY HotelName

The above would eliminate the need for a second data table, and eliminate the code in the row data bind event.