locked
How to query the sum of a column using search input RRS feed

  • Question

  • User-1557173817 posted

    Hello please how can i query the sum of a column by date using an input filed and button, am using webmatrix 

    Friday, August 28, 2015 9:03 AM

Answers

  • User325035487 posted

    Assuming you have an input field in your form like this

    <input type="date" name="filterdate" />

    in your code block

    if (IsPost)
    {
      var datefromform = Request["filterdate"] + " 00:00:00";
      DateTime date = DateTime.Parse(datefromform , new CultureInfo("en-GB").DateTimeFormat);
    
    var sql = "SELECT SUM(CAST(Amount AS FLOAT)) AS Total FROM TableName WHERE DateColumn=@0";
    var total = db.QueryValue(sql,date)
    }

    This is assuming youve a datetime data type for DateColumn and the input type returns a valid date of the formats dd/MM/yyyy or similar

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 28, 2015 10:54 AM

All replies

  • User325035487 posted

    Assuming you have an input field in your form like this

    <input type="date" name="filterdate" />

    in your code block

    if (IsPost)
    {
      var datefromform = Request["filterdate"] + " 00:00:00";
      DateTime date = DateTime.Parse(datefromform , new CultureInfo("en-GB").DateTimeFormat);
    
    var sql = "SELECT SUM(CAST(Amount AS FLOAT)) AS Total FROM TableName WHERE DateColumn=@0";
    var total = db.QueryValue(sql,date)
    }

    This is assuming youve a datetime data type for DateColumn and the input type returns a valid date of the formats dd/MM/yyyy or similar

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 28, 2015 10:54 AM
  • User1313602441 posted
    I think you should also use the form to get the date the user wants.
    Then, you can use something like this:
    var usercolumn = Request.QueryString["usercolumn"];
    var userdate = Request.QueryString["userdate"];
    var command = "select SUM(column) from table order by where columnname like = @0 AND datecolumn = @1";
    var get = db.QueryValue(command, usercolumn, userdate);

    Then you can output the result like this:
    <p>@get</p>
    Friday, August 28, 2015 11:01 AM
  • User325035487 posted

    I wont work for a form.

    var usercolumn = Request.QueryString["usercolumn"];
    var userdate = Request.QueryString["userdate"];

    Those look for a string in the url like this http://site?usercolumn=somevalue&userdate=12/08/2015

    select SUM(column) from table order by where columnname like = @0 AND datecolumn = @1

    The Order By clause should be after WHERE Condition for that to work

    Friday, August 28, 2015 1:21 PM
  • User1313602441 posted
    @jkjhe
    I had written my post 3 minutes before you posted yours. But my network was giving me issues. It was when I saw your anwser that I realized my mistakes.
    Thanks...
    Saturday, August 29, 2015 2:11 AM