How to subtract two values to give me a total and grand total
-
I want to subtract two different numbers to get a total and a grand total from each column.
Here is what i have tried:
Create Table Savings(ID int, afbc money, afoc money, afsav money, ofbc money, ofoc money, ofsav money etc...
stored Procedure:
AS Select ID, sum(afbc)- sum(afoc) as ofsav,
sum(ofbc)- sum(ofoc) as ofsav,
afbc, afoc, ofbc, ofoc
from savings
order by ID
(I also want to add afbc and ofb; ofbc and ofoc; afsav and ofsav to give a total in each column.
-
I'm not *quite* certain what you are attempting here - SUM works across rows, and you are using it within a single row?
Perhaps a small dataset showing what you want your resulting output to look like? You don't need all of the data columns, but include at least afbc, afoc, ofbc, ofoc, and ofb, and show the total values you want to generate, using (say) a three-row demo dataset.
-
I agree with brendthess...you need to post some sample data at the very least...
it seems to be that you don't need the sum() at all and just (afbc - afoc) etc...should give you what you need....unless your ID is not unique and you want sums of columns grouped by ID ?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
I agree with brendthess...you need to post some sample data at the very least...
+1
Here is a good reference: Providing details
-
This is just a guess here but it might be what you need :
Declare @ofsav1 as int
Declare @ofsav2 as int
Select @ofsav1 = sum(afbc)- sum(afoc), @ofsav2 = sum(ofbc)- sum(ofoc)
from savings
then :
Select *, @ofsav1 as O1, @ofsav2 as O2 from dbo.Savings order by id.
You could also return those values as output parameters to save on bandwith.
-
what an eager beaver you are remi - just can't wait, can you?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Seems logical!!!
-
I have 3 columns of data going 3 across for the totals: Example
category Brokers cost our price total savings
application fee afbc (1$,200) afoc ($900) afsav ($300)
Origin. Fee ofbc ($1,400) ofoc ($800) ofsav ($600)
Total: ($2,600) ($1,700) ($900)
This is the stored procedure I wrote. I want to enter the data on a form shown just like this and once I enter the costs it will automatically calculate the savings in the total savings column and the totals at the bottom. The thing is, I had to import a subform to hold the brokers cost and our price columns and have a refresh page macro to get it to update. I need it to do it automatically.
(this is not updatable on a form)
AS Select ID, sum(afbc)- sum(afoc) as ofsav,
sum(ofbc)- sum(ofoc) as ofsav,
afbc, afoc, ofbc, ofoc
from savings
-
You just can't do it that way... I'd have one proc for the data and a separate one for the totals that would be loaded on the form_load()... simplest solution to your problem.
-
This seems more like a presentation layer problem -- can't you simply add the two columns in the form for the totals. If you need this information in the table, you might add a calculated field.
-
Youguys are right. I'll make form and then a procedure to run the totals. Thanks!!!
-
Well everything worked but I couldn't get the the savings column to total. Here is the script I wrote:
SELECT ID, LOBC, LOOC, SUM(LOBC) - SUM(LOOC) AS LOsav, DisBC, SUM(DisBC) - SUM(DisOC) AS Dissav, DisOC, BFBC, BFOC, SUM(BFBC) - SUM(BFOC)
AS BFSAV, PFBC, PFOC, AdFBC, AdFOC, ApFBC, ApFOC, YSDBC, YSDOC, IRBC, IROC, SUM(PFBC) - SUM(PFOC) AS PFsav, SUM(AdFBC) - SUM(AdFOC)
AS AdFsav, SUM(ApFBC) - SUM(ApFOC) AS APFsav, SUM(YSDBC) - SUM(YSDOC) AS YSDsav, SUM(IRBC) - SUM(IROC) AS IRsav, SUM(LOBC)
+ SUM(DisBC) + SUM(PFBC) + SUM(AdFBC) + SUM(ApFBC) + SUM(IRBC) + SUM(YSDBC) + SUM(BFBC) AS TotBC, SUM(LOOC) + SUM(DisOC)
+ SUM(PFOC) + SUM(AdFOC) + SUM(ApFOC) + SUM(IROC) + SUM(YSDOC) + SUM(BFOC) AS TotOC, SUM(LOsav) + SUM(Dissav) + SUM(PFsav)
+ SUM(AdFsav) + SUM(ApFsav) + SUM(IRsav) + SUM(YSDsav) + SUM(BFsav) AS Totsav
FROM dbo.SavingsEst
GROUP BY ID, LOBC, LOOC, DisBC, DisOC, BFBC, BFOC, PFBC, PFOC, AdFBC, AdFOC, ApFBC, ApFOC, YSDBC, YSDOC, IRBC, IROC
The BC extention is the Broker's Cost column and OC is Our Cost column. I did those with a subform and the savings total and Bc, Oc totals as a strored procedure. Anyone have any ideas for an easier way. Thanks!
-
Maybe this is more complicated than I see at first blush but could it be as simple as a union?
SELECT Category, BrokersCost, OurPrice, Brokerscost - Ourprice AS [Total Savings]
FROM TableName
UNION
SELECT 'Totals' AS Category, SUM(BrokersCost) AS BrokersCost, SUM(OurPrice) AS OurPrice, SUM(Brokerscost - Ourprice) AS [Total Savings]
FROM TableName
Results:
Category BrokersCost OurPrice Total Savings
Application Fee 1200 900 300
Origin. Fee 1400 800 600
Totals 2600 1700 900
-
The categories are just labels on a form. The numbers are just values that I'm entering on the form. I don't think that the way you wrote it will actually work the way I need it to. I could be wrong of course.
-
If you're working in Access, in the form itself, why not just add the logic to the summary text box like so...
Control Source: =Val([text1])+Val([text2])
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply