Trying To Display Average by Dividing Each Column by the Sum of the Same Column in a GridView with ASP.Net

Question

I need to display the average of clicks in a percentage inside a Gridview I already formatted to show the Index and Clicks. I need to divide the number in each "Clicks" column by the sum of the same "Clicks" column.

Index   Clicks   %
       
| 1 |  | 24 | | 44% |          
| 2 |  | 31 | | 56% |

Here is my GridView

user_920362 · Answer

SELECT DISTINCT
    LinkId [Index],
    Count(*) over (Partition by LinkId) [Clicks],
    Convert(VARCHAR(10), count(*) over (Partition by LinkId) * 100 / count(*) over (Partition by 1)) + '%' [Percent]
FROM
    Track_Click_7
WHERE
    CampaignId = 18444

I would start by trying to solve in the SQL Statement, like above.. Probably not the best solution though, but an idea.

user_10527 · Answer

You can do this quite a few different ways.

but, easy way?

Simple declare a PUBLIC var at the page class level, and then use that as a expression in the markup.

So, your typical code to load a grid will look like this:

    public double TotalClicks = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid()
    {
        // load up our grid
        using (SqlCommand cmdSQL = new SqlCommand("SELECT TOP 15 * from tblHotels ORDER BY HotelName ",
            new SqlConnection(Properties.Settings.Default.TEST4)))
        {
            cmdSQL.Connection.Open();
            DataTable rst = new DataTable();
            rst.Load(cmdSQL.ExecuteReader());
            TotalClicks = Convert.ToDouble(rst.Compute("SUM(Clicks)", ""));
            GridView1.DataSource = rst;
            GridView1.DataBind();
        }
    }

So, now when you load the table, just total that column - set to a public var for that page.

Now, you can simple display the % in the grid markup like this:

So total grid markup looks like this:

Output:

Trying To Display Average by Dividing Each Column by the Sum of the Same Column in a GridView with ASP.Net

2 Answers