Solved

Using SUM in Coldfusion (or Report Builder)

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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 add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

920 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

16 Experts available now in Live!

Get 1:1 Help Now