Solved

Adding Up Columns

Posted on 2002-07-23
11
115 Views
Last Modified: 2013-12-24
I need to add up columns of numbers from a query..  I do have it adding up but its adding up wrong.  Is there a way to zero out the number to make sure that is adding correctly or can someone give me a better solution for adding columns of numbers.. Here is my current query:

<cfquery name="getTotals" datasource="#DataTables#">
Select
     SUM(NetAmount) AS NetAmountTotal,
     SUM(ActualPages) AS ActualPagesTotal,
     SUM(EquivPages) AS EquivPagesTotal,
     SUM(HonAd) AS HonAdTotal,
     SUM(ProgAd) AS ProgAdTotal
FROM AdPages
WHERE adpages.issue_id = #URL.ID# AND adpages.cancelled = 0
</cfquery>

Thanks
0
Comment
Question by:jeffmace
  • 4
  • 4
  • 3
11 Comments
 
LVL 5

Expert Comment

by:CFXPERT
Comment Utility
Actually I don't see any problems with using the SUM funciton like your doing.  When you say it's not adding these columns up correctly, how much is it off by.  Maybe there's some other criteria involved here.
0
 
LVL 5

Accepted Solution

by:
CFXPERT earned 200 total points
Comment Utility
Are you sure that all the data in the columns are valid numbers?  That would be one thing to check out.  I don't see how they couldn't be cause the database wouldn't let you insert a value that could not be converted to a number if the datatype is number.
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
Hi Jeffmace,

Pls chk ur

1. datatype
2. data [null / decimals / long values - might get truncated ]
3. Try using caste / convert function to get thru with this.

the above should help.

K'Rgds
Anand
0
 

Author Comment

by:jeffmace
Comment Utility
Acutally what I found out late last night was that it was a query problem with my INNER JOINS that were causing the issue...  I corrected it
0
 
LVL 5

Expert Comment

by:CFXPERT
Comment Utility
I don't see any INNER joins in your posted SQL at the top of this forum.  We were trying to help you based on what you posted.

:) KWK
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:anandkp
Comment Utility
Hi Jeffmace,

I fully agree to what CFXPERT has posted above ... the help given is on the basis of what u ask on the site - as we have no clue whatsoever - the actual problem is ...

K'Rgds
Anand
0
 

Author Comment

by:jeffmace
Comment Utility
Sorry if i didn't explain clearly... What I found out from the problem of the user was that the total was adding fine but it wasn't displaying all the records about it... for this example it was suppoed to have 15 records adding up to a total... well the total was correct but it was displaying only 13 records.

So I have a query above this that displays the records and then this query adds the records.
0
 

Author Comment

by:jeffmace
Comment Utility
If either or you want to argue out who gets the points i will be more than happy to distribute them..
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
Hi Jeff,

Since u have to award point to someone - award it to the person whom u think - was closests to the answere ...

As i dont think we guys would want to argue out on this !!!

So its upto U !!!

K'Rgds
Anand
0
 

Author Comment

by:jeffmace
Comment Utility
Thanks for your help
0
 
LVL 5

Expert Comment

by:CFXPERT
Comment Utility
Not a problem jeff.  Just make sure that when you post questions try to be as exact as possible.  If you feel that you may know where the problem might be then make sure post all the code needed by us to help you out.

Thanks for the points.

:) KWK
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

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…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

6 Experts available now in Live!

Get 1:1 Help Now