You must Sign In to post a response.
  • Category: ASP.NET

    How to Calculate the Total of a Table Particular Column Row Values in C#.

    hi Developers i have a table Called tblPrice

    i want to Calculate Calculate the Total Table Particular Column Row Values.
    in my tblPrice table if i have values like

    Id Name Cost
    1 Mobile 1000
    2 T.V. 2000
    3 Camera 500

    My Expected the Output is should be 3500. (Cost Column Row[1]+Row[2]+Row[3])
    i am try to do i did't get exact output. so please help me to how i done my Requirement . I have no time so i can't able to try more

    thanking You
    Paul.S
  • #768335
    anyway thanks friends i got exact output from the follwing query

    SqlConnection con = Connection.DBConn();
    DataTable dt = new DataTable();
    SqlCommand cmd = new SqlCommand("select * from test", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);
    // dt is datatable and by using Compute Property we are calculating Sum of Price Column
    string sum = dt.Compute("Sum(name)", "").ToString();
    lbl.Text = sum.ToString();

    PAul.S

  • #768340
    Hi,

    We can also do this in Excel sheet itself without going to database.


    Worksheet.Cells[10, 6].Formula =
    "=Sum(" + Worksheet.Cells[5, 26].Address +
    ":" + Worksheet.Cells[rowIndex, 26].Address + ")";


    The above is for the directly check the values from Excel sheet, So we open the excel sheet from application and see the values present in particular column.

    Thanks,
    Mani

  • #768341
    You can use SUM command to calculate total of a particular table
     int sum = Convert.ToInt32(dt.Compute("SUM(Salary)", "EmpId > 10));
    Or
    int sum = dt.AsEnumerable().Where(row => row.Field<int>("EmpId") > 2).Sum(row => row.Field<int>("Salary"));

    Here is the code snippet for your problems
    Dim dt As New DataTable()
    dt.Columns.AddRange(New DataColumn(2) {New DataColumn("EmplId", GetType(Integer)),
    New DataColumn("Name", GetType(String)),
    New DataColumn("Salary", GetType(Integer))})
    dt.Rows.Add(1, "Mohan", 43000)
    dt.Rows.Add(2, "Sunil", 32000)
    dt.Rows.Add(3, "Rani", 18000)
    dt.Rows.Add(3, "Suzanne ", 18500)

  • #768459
    Hi,

    In your sql query itself you can perform the same operation using SUM method.


    select SUM(cost) from tablename

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768462
    with he help of datatable and LINQ you can able to accomplish your task, see below snippet

    protected void Page_Load(object sender, EventArgs e)
    {
    if (!this.IsPostBack)
    {
    this.BindGrid();
    }
    }

    private void BindGrid()
    {
    string query = "SELECT TOP 30 OrderID,";
    query += "(SELECT ProductName FROM Products WHERE ProductID = details.ProductId) ProductName,";
    query += "(Quantity * UnitPrice) Price";
    query += " FROM [Order Details] details";
    query += " ORDER BY OrderID";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
    using (SqlCommand cmd = new SqlCommand(query))
    {
    using (SqlDataAdapter sda = new SqlDataAdapter())
    {
    cmd.Connection = con;
    sda.SelectCommand = cmd;
    using (DataTable dt = new DataTable())
    {
    sda.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();

    //Calculate Sum and display in Footer Row
    decimal total = dt.AsEnumerable().Sum(row => row.Field<decimal>("Price"));
    GridView1.FooterRow.Cells[1].Text = "Total";
    GridView1.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Right;
    GridView1.FooterRow.Cells[2].Text = total.ToString("N2");
    }
    }
    }
    }
    }

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments