Solved

Using SUM in Coldfusion (or Report Builder)

Posted on 2006-11-21
5
1,099 Views
Last Modified: 2013-12-20
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

</cfquery>

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!!!

0
Comment
Question by:diecasthft
  • 2
5 Comments
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17990793
try this query and see what you get

<cfquery name="Region" datasource="LMR_Oracle">
SELECT INSTALLATION_OVERVIEW.INSTALLATION_NAME,
       SYSTEM_COST_OVERVIEW.YEAR,
         SUM(MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE) AS TOTAL,
         MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE

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 SYSTEM_COST_OVERVIEW.YEAR, INSTALLATION_OVERVIEW.INSTALLATION_NAME, MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE
</cfquery>
0
 

Author Comment

by:diecasthft
ID: 17994892
That didn't do it....actually that's what I thought it should be to get my results, but when I do this, I get the results listed above......the sum doesn't seem to work when I add the specific MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE field to the query.
0
 

Author Comment

by:diecasthft
ID: 17995228
I think I figured it out.....below is what I came up with for a query......

SELECT INSTALLATION_OVERVIEW.INSTALLATION_NAME,
       SYSTEM_COST_OVERVIEW.YEAR, MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE,
        (SELECT SUM(MAINTENANCE_CONTRACT_COST.ANNUAL_COST_OF_SERVICE) FROM (LMR.MAINTENANCE_CONTRACT_COST) WHERE
(SYSTEM_COST_OVERVIEW.ARMY_INSTALLATION_NAME = 'Frederick'
AND INSTALLATION_OVERVIEW.INSTALLATION_NAME = MAINTENANCE_CONTRACT_COST.INSTALLATION_NAME)) AS TOTAL

it seems to give me what I need for the values expected....now I wonder what will happen as I add more.......


0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18309937
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now