locked
HOW TO SUM COLUMNS IN SQL CE WITH WEBMATRIX RRS feed

  • Question

  • User-1557173817 posted

    HELLO, PLEASE HOW CAN I SUM A COLUMN IN SQL CE WITH WEBMATRIX

    Monday, August 24, 2015 4:17 AM

Answers

  • User325035487 posted

    My datatype for Amount is nchar and dont have text in it

    SELECT SUM(CAST(Amount AS FLOAT)) AS Total FROM TableName
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 26, 2015 12:06 PM

All replies

  • User325035487 posted
    SELECT SUM([Column Name])AS Total FROM TableName

    if you have a condition to check for , then

    SELECT SUM([Column Name])AS Total FROM TableName WHERE Condition=Value

    Monday, August 24, 2015 9:36 AM
  • User-1557173817 posted

    thanks a million 

    Monday, August 24, 2015 10:11 AM
  • User-1557173817 posted

    Please i also need a comment section on my website, how will i go about it am using webmatrix

    Monday, August 24, 2015 10:13 AM
  • User325035487 posted

    Use Facebook helper. Or you have to write the code yourself to save to local database. In either case it is beyond the scope of this original thread. If you have some specific issue post as a separate thread. Mark as answer if your original question was resolved.

    Monday, August 24, 2015 10:49 AM
  • User-1557173817 posted

    hi i just ran this code am receiving an error response : System.Collections.ObjectModel.ReadOnlyCollection`1[System.Object]

      var db = Database.Open("StarterSite");
     
      var selectCommand = "SELECT SUM([Amount]) AS Total FROM act ";
      var searchTerm = "";
                   
        if (!Request.QueryString["searchName"].IsEmpty() )
          {
              selectCommand = "SELECT SUM([Amount]) FROM act WHERE CreationDate=@0";
            searchTerm = "%" + Request["searchName"] + "%";
           }
                
        var selectedData = db.Query(selectCommand, searchTerm);

    html : @selectedData

     

    Tuesday, August 25, 2015 6:00 AM
  • User325035487 posted

    Dan Zara

    var selectedData = db.Query(selectCommand, searchTerm);
    html : @selectedData

    Instead of that two lines

    var selectedData = db.QueryValue(selectCommand, searchTerm)
     <p>Sum: @selectedData</p>

    To display.. use any html span tags

    This db.QueryValue as your query is returning s single value.

    db.QuerySingle if a single row with multiple columns

    db.Query if multiple rows expected

    Tuesday, August 25, 2015 6:50 AM
  • User-1557173817 posted

    Hello i try that and it worked but thing is that it sum all the column instead of the entries in the column. The thing is that i wanted a code to sum all total amount in a column called Amount for an invoice database eg i want something like this:

    Fees And School Hours

    Amount

    Ages

    Application Fees

    N5,000

     

    Infant Centre (7am – 5pm)

    N 3,5000

    (Tuition)

    4 -12 Months

    Toddlers Class (8am – 1pm)

    N 80,000

    (Tuition)

    1 – 3 Year(s)

    Material & Supply Fee

    N 10,000

    (Toddlers)

     

    School Cardigan

    N 3,000

     

    School Spot Wear

    N 1,500

     

    Developmental Levy

    N 10,000 (Once)

     

    School Photograph

    N 2,500

     

    Extended Hours

     (2pm – 7pm)

    N 1,000 (Per Day)

     

    Medical Insurance

    15000

     

    Total: 140,500.00

    So instead of having 140,500 am having 10 which is the number of columns.   Please help me....thanks

    Wednesday, August 26, 2015 5:01 AM
  • User325035487 posted

    Whata your data type for the column Amount?

    You have text in it like (once) (Tuition)(Toddlers) etc?

    I set an example table with amount as int type here. You might want to go for a float data type

    See here

    http://sqlfiddle.com/#!3/9109a/1

    Wednesday, August 26, 2015 6:20 AM
  • User-1557173817 posted

    My datatype for Amount is nchar and dont have text in it

    Wednesday, August 26, 2015 9:42 AM
  • User-1557173817 posted

    Ok, i noticed you used int as ur data type for amount, but why is mine giving me column numbers

    Wednesday, August 26, 2015 9:47 AM
  • User325035487 posted

    My datatype for Amount is nchar and dont have text in it

    SELECT SUM(CAST(Amount AS FLOAT)) AS Total FROM TableName
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 26, 2015 12:06 PM
  • User1313602441 posted
    I think it's best you use "money" as your datatype.
    Thursday, August 27, 2015 9:27 AM
  • User-1557173817 posted

    Thanks it worked, how can i display the table and have the sum under it.....will also appreciate a code that will query the  database using date along side an input field. 

    Friday, August 28, 2015 5:00 AM
  • User325035487 posted

    Mark as answer if your original question was resolved. Please make seperate thread for different issues

    Friday, August 28, 2015 7:36 AM
  • User-1557173817 posted

    Alright, thanks.

    Friday, August 28, 2015 8:49 AM