Can't output aggregate query 1

[RockbridgeBob]

I have a query as follows:

<cfquery name=&quot;KAMLate&quot; datasource=&quot;directory&quot;>
select sum(TotalLate) as tLate from AttendanceSum where Room='K-am' and Date=#Date#
</cfquery>

It does not give any errors.

The purpose is to total all of the values in the TotalLate field. The problem is how do I get the answer out?

I've used queries and CFoutput a lot, but have never tried SQL aggregate functions such as SUM().

 

[tleish]

Try this:

<CFOUTPUT>
#KAMLate.tLate#
</CFOUTPUT> - tleish

 

[RockbridgeBob]

Actually I have tried that with no success. The page processes without error, but also with nothing showing in the location that CFOUTPUT is located.

 

[tleish]

That means that there are no records in the query.
<cfquery name=&quot;KAMLate&quot; datasource=&quot;directory&quot;>
select sum(TotalLate) as tLate
from AttendanceSum
where Room='K-am' and Date=#Date#
</cfquery>

The above query will always return RecordCount of 1 regardless if there are any records or not and KAMLate.tLate will be blank if there aren't any records.

Try this:
<cfquery name=&quot;KAMLate&quot; datasource=&quot;directory&quot;>
select TotalLate
from AttendanceSum
where Room='K-am' and Date=#Date#
</cfquery>

<cfif KAMLate.recordCount>
<cfquery name=&quot;KAMLate&quot; datasource=&quot;directory&quot;>
select sum(TotalLate) as tLate
from AttendanceSum
where Room='K-am' and Date=#Date#
</cfquery>
<cfoutput>
#KAMLate.tLate#
</cfoutput>
<cfelse>
No records found
</cfif> - tleish

 

[RockbridgeBob]

Thanks!
That last suggestion was the solution. It seems that the date that I was using was not being interpreted correctly, so no records matched, and no sum was being calculated. However, the query would respond with 1 record - a blank one.

Your code helped to clear that up.

Thanks again