?
Solved

Using SUM in Coldfusion (or Report Builder)

Posted on 2006-11-21
5
Medium Priority
?
1,116 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
4 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
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 insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

850 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