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
Solved

Using SUM in Coldfusion (or Report Builder)

Posted on 2006-11-21
5
1,105 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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

Suggested Solutions

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
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. …

839 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