Solved

Using SUM in Coldfusion (or Report Builder)

Posted on 2006-11-21
5
1,109 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
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…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

718 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