Using SUM in Coldfusion (or Report Builder)
Posted on 2006-11-21
I'm really confused, or really dumb, but I have a problem getting the correct output when using SUM in Coldfusion. I have an Oracle database with several costing tables. What I want to do is take certain costs, from certain tables, and come up with a cumulative sum. So in my database, I have a cost for maintenance at 195001 for the year 2007, and another cost for 45000 for the year 2006. The code I have below does the sum the way I want...I think....
<cfquery name="Region" datasource="LMR_Oracle">
SELECT DISTINCT INSTALLATION_OVERVIEW.INSTALLATION_NAME, SYSTEM_COST_OVERVIEW.YEAR, SUM(MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE) AS TOTAL
FROM LMR.INSTALLATION_OVERVIEW, LMR.SYSTEM_COST_OVERVIEW, LMR.MAINTENANCE_CONTRACT_COST
WHERE INSTALLATION_OVERVIEW.INSTALLATION_NAME = 'Frederick'
AND SYSTEM_COST_OVERVIEW.INSTALLATION_NAME = INSTALLATION_OVERVIEW.INSTALLATION_NAME
AND MAINTENANCE_CONTRACT_COST.INSTALLATION_NAME = INSTALLATION_OVERVIEW.INSTALLATION_NAME
GROUP BY INSTALLATION_OVERVIEW.INSTALLATION_NAME, SYSTEM_COST_OVERVIEW.YEAR
The result I get is
INSTALLATION_NAME YEAR TOTAL
Frederick 2007 240001 ------------ This is right.
Frederick 2006 240001
But what I also need to do is have the initial cost field in the query......so I thought I would add the MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE to my select statement without the SUM....hoping it would give me my initial numbers that total the 240001 in the SUM calculation. But it doesn't....in fact, it gives me my initial numbers in both clumns, and doesn't do the SUM at all....it also doubles the entries:
INSTALLATION_NAME YEAR TOTAL ANNUAL_COST_OF_SERVICE
Frederick 2007 195001 195001.00
Frederick 2006 45000 45000.00
Frederick 2007 45000 45000.00
Frederick 2006 195001 195001.00
I'm at a total lost as to where to go from here.....this all started because I was trying to build a report in Report Builder and wasn't getting the right results, so I suspected my query was wrong, and obviously it is somewhere along the way. My problem is that I have several instances of this..I didn't want to have a field in my database with totals, I wanted to get the totals at query/report time based on the data in the database, and that data would come from different tables. Any help would be GREATLY appreciated!!!