You must Sign In to post a response.
  • Category: SQL Server

    How to Perform Running Total Query

    Hi
    Experts

    I need how to perform row wise running Total in Sql Query for my Crystal report

    I have attached Excel sheet any one solve this issue.
  • #761949
    Hi
    Experts

    I have attached Excel sheet given below.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    RunningTotals.xls

    Delete Attachment

  • #761950
    Hello Kumar,

    You want the total calculation in Crystal Report or sql total in Crystal Report.??

    Can you just elaborate it.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #761952
    Hi
    Nirav

    I need pure My sql Query only.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #761953
    hi
    Nirav

    check the attachment and send me pure mysql query
    only.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #761973
    Hello Kumar,

    As much i understood you can use
    int noofrows = 0, prevbal = 0;
    MySqlConnection con = new MySqlConnection("connection string");
    con.Open();
    MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Table1");
    MySqlDataReader dr = cmd.ExecuteReader();
    if(dr.HasRows)
    {
    dr.Read();
    noofrows = Convert.ToInt32(dr[0].ToString());
    dr.Close();
    }
    con.Close();

    con.Open();
    for(int i =1; i <= noofrows; i++)
    {
    if(i == 1)
    {
    cmd = new MySqlCommand("SELECT Sno, ShareNo, Date, Name, Recmat, Payamt, Total(OpeningBalance+Recamt-Payamt), OpeningBalance");
    dr = cmd.ExecuteReader();
    if(dr.HasRows)
    {
    dr.Read();
    prevbal = Convert.ToInt32(dr[6].ToString())
    dr.Close();
    }
    }
    else
    {
    cmd = new MySqlCommand("SELECT Sno, ShareNo, Date, Name, Recmat, Payamt, Total(OpeningBalance+" + prevbal + "+Recamt-Payamt), OpeningBalance");
    // Your code goes here
    }
    }
    con.Close();


    Kumar i have not tested this query because my system is in under maintenance.

    Hope this will work for you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #761989
    You can try the CTE - trick for solve this.

    1. Select all the Receipt and Payment and store using CTE for the From date.

    2. Select the opening balance from the CTE using that opening balance you can do the running total.

    By Nathan
    Direction is important than speed


Sign In to post your comments