HOW TO SUM COLUMNS IN SQL CE WITH WEBMATRIX

Question

User-1557173817 posted

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

Answer 1

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

Answer 2

User-1557173817 posted

thanks a million 

Answer 3

User-1557173817 posted

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

Answer 4

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.

Answer 5

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

 

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

User-1557173817 posted

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

Answer 10

User-1557173817 posted

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

Answer 11

User325035487 posted

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

SELECT SUM(CAST(Amount AS FLOAT)) AS Total FROM TableName

Answer 12

User1313602441 posted
I think it's best you use "money" as your datatype.

Answer 13

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. 

Answer 14

User325035487 posted

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

Answer 15

User-1557173817 posted

Alright, thanks.