This is what I have. It was answered a few days ago, but I deleted the file accidentally ŭere I solved the problem. <cfquery name="expense" datasource="expense_db" username = "foo" password = "bar"> SELECT amount, expense_date, description, sum(amount) FROM expense </cfquery> I then have <cfoutput query="expense"> #sum(amount)# </cfoutput> The error I got was "No matching function [SUM] found" but that didn't happen last time. I forgot what I did!

You'll have to write the

sum()

function.. Regarding setting it to a variable...

<cfset myVar = sum(total) />

You're query could use an alias

sum(amount) as 'total'

then you can reference it by #total#

Daniel's solution is better; I missed the

sum(amount)

in the query

Didn't you ask about this on StackOverflow? If yes, you might want to check the responses as someone answered 2 days ago 😆

I can't seem to put #total#.

Please elaborate on that. What do you mean by "put #total#"? What happens? Did you use the alias as suggested?

100 + #total# doesn't work. total is the alias

What do you mean by "doesn't work"? Is there an error message?

variable [TOTAL] doesn't exist

Does it need to be scoped? Is it a query column?

it's a column. I seem to be confusing sql with cf. So I how do I make a column a variable? if that's what I need?

Did you figure it out?

So basically, I want to subtract the value of #sum.amount# from let's say 100. It seems easy enough, but I'm getting stuck somewhere

Unfortunately, you haven't provided enough information for us to help

What do you need?

More context. More code samples, like on SO, a minimal reproducible example. Any error or debugging information.

<cfquery name="expense" datasource="expense_db" username = "foo" password = "bar"> SELECT amount, expense_date, description, sum(amount) as total FROM expense </cfquery> I want to subtract sum(amount) from let's say 100. errror message says TOTAL doesn'rt exist

What are you attempting? What happens? Is there an error message? Are you doing that for each query record?

i replied

If you are trying to combine multiple records to get the sum, you are going to need to use GROUP BY - For example, if I wanted each day's items with the same description, I'd do something like this in the SQL: SELECT expense_date, description, sum(amount) as total FROM expense GROUP BY description, expense_date ORDER BY description, expense_date

I understand that, but you haven't shared any substantial code in which you attempt to use

#total#

. Are you doing this for each query record? Does it need to be scoped?

Total expenses <b>#total#</b> Money remaining <cfset remaining = 1000 - #total#> #remaining#

does

total

show in your bold tags?

yes

You need to refer to it by the query name probably... #Expense.Total#

to answer myka, it just prints out the word #TOTAL# in bold

Variables must be wrapped in

<cfoutput>

(or the like) to be evaluated and displayed. If you are attempting to iterate through multiple records in a query, use

<cfoutput query="yourQueryName">

<cfquery name="expense" datasource="expense_db" username = "foo" password = "bar">     SELECT  expense_date, description, sum(amount) as total     FROM    expense     GROUP BY    description, expense_date     ORDER BY    description, expense_date </cfquery> <!--- Loop through every row of the query above ---> <cfoutput query="expense">     <b>#Expense.Total#</b>     Remaining: #1000-Expense.Total# </cfoutput> <!--- Reference only the first result from the query above     ColdFusion Variables will only be rendered when inside of CFOUTPUT ---> <cfoutput>     <b>#Expense.Total#</b>     Remaining: #1000-Expense.Total# </cfoutput> <!--- Use CFLOOP to output each row of the query inside of a CFOUTPUT     ColdFusion Variables will only be rendered when inside of CFOUTPUT ---> <cfoutput>     <cfloop query="expense">         <b>#Expense.Total#</b>         Remaining: #1000-Expense.Total#     </cfloop> </cfoutput> <!--- This code should check that it is actually numeric before trying to do math 😉 --->

thanks! seems rather complicated for something simple enough

It will make sense eventually...