Add two columns to make one total

Question

Add two columns to make one total

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246977 More actions · Answer 1

Jason-299789 (3/15/2016)
Phil Parkin (3/15/2016)
Jason-299789 (3/15/2016)
@yb751
The NULL will only be eradicated if there is a row with a non-Null value, in the same column.
See the code below, when aggregating on Account.
DECLARE @myTable TABLE (Account int,Amount INT, OrgAmount INT)
INSERT INTO @myTable
VALUES (1, 50, 100), (1,10, NULL), (2,NULL, 40), (3,120, 30)
SELECT
Account , SUM(Amount) + SUM(OrgAmount) AS TotalAmount
FROM
@myTable
group by Account
While I agree that NULLs are handled when SUMming, personally I do not like seeing the
Null value is eliminated by an aggregate or other SET operation.
warning message & therefore generally choose to code around it.
Phil I agree I was attempting to show why you still needed the ISNULL as you cant be 100% certain of the data unless there are NOT NULL constraints.
So as you say the best way is to use a belt an braces approach that way shouldn't cause too many issues.

Sorry, Jason, I didn't read your post closely enough. I should probably have replied to Gail's instead. But if I do that, I'm usually proved wrong 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246977 More actions · Answer 2

lkennedy76 (3/15/2016)
I put the ISNULL back and got amounts back but I am at the same cross road, the second column (OrgAmount) is not adding up to one amount.

I don't know what you mean by "adding up to one amount".

Can you provide sample DDL and data, along with expected results, as described in the link in my signature?

Only then will we definitely be able to resolve this for you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Virtual Reality Ten Centuries Points: 1182 More actions · Answer 3

I didn't try this but as per my previous issue I used computed column properties of that column.

drew.allen SSC Guru Points: 76996 More actions · Answer 4

lkennedy76 (3/15/2016)
I took out the ISNULL, the SUM function requires a group by so I grouped it by ID not the amounts, all amounts are now NULL

As Gail already said, SUM does not require a GROUP BY. A GROUP BY is only required when you are mixing aggregated and unaggregated data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

lkennedy76 SSChampion Points: 13683 More actions · Answer 5

I should have put the whole query out here. I am mixing so I need to group. Here is the whole query;

select

p.Date as [Payment to Carrier Date],

t.ConfirmationID as [Confirm Number],

t.SDate as [Submit Date],

c.LastName as [Customer Last],

c.FirstName as [Customer First],

c.tin,

pr.PName as [Product],

(select ac.Description from AccountClasses ac where a.AccountClass_ID = ac.ID) as [Plan Type],

--(p.Amt + p.OrgAmt) as [Payment Amount],

PaymentAmount = SUM(ISNULL(p.Amt, 0)) + SUM(ISNULL(p.OrgAmt, 0)),

co.name as [Company Name]

from accounts a left join

transactions t on a.ID = t.Account_ID left join

products pr on a.Product_ID = pr.ID left join

Companies co on a.Company_ID = co.id left join

customers c on t.Customer_ID = c.id left join

payments p on t.id = p.Transaction_ID

where t.Status_ID = 1 and p.Date = '02/05/2016'

Group by p.Date, t.ConfirmationID, co.name, t.SDate, c.LastName, c.FirstName,

c.TIN, pr.PName, a.AccountClass_ID

order by co.name, c.LastName

I have attached some screen shots, the 12443.93 is the OrgAmt column, it is missing two other amounts added in, the total should be 31459.93. The other screen shot is the Amt coulmn adding the amounts correctly.

MCSE SQL Server 2012\2014\2016

Y.B. SSChampion Points: 11649 More actions · Answer 6

Jason-299789 (3/15/2016)
@yb751,
I appreciate that but you write a query in one instance to do specific job then someone comes up with a bright idea of expanding the grain, so you quickly add the additional columns and wonder why the numbers don't stack up when you encounter that situation.
Try and anticipate future requirements, especially if aggregating because someone will want to see the data in finer detail.

Jason, I'm not sure what you mean here. I clearly understood the good point you made, hence why I added code that included code with ISNULL. I simply listed 3 different ways of showing the results so that the OP could see the different nuances and how it would affect the result set.

SELECT quote FROM brain WHERE original = 1
0 rows returned

lkennedy76 SSChampion Points: 13683 More actions · Answer 7

After a long hard battle of 'I don't wanna', the temp table is the best option in this scenario.

Thank everyone for all your replies. I am truly grateful for this forum in times of getting new gray hair!

MCSE SQL Server 2012\2014\2016

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 8

Jason-299789 (3/15/2016)
@yb751,
I appreciate that but you write a query in one instance to do specific job then someone comes up with a bright idea of expanding the grain, so you quickly add the additional columns and wonder why the numbers don't stack up when you encounter that situation.
Try and anticipate future requirements, especially if aggregating because someone will want to see the data in finer detail.

Yes and no. While I agree with anticipation of future requirements and scalability, if you were to truly anticipate future requirements, you would have a full up GROUP BY WITH CUBE and use GROUPING to identify all the different row types and include cumulative totals for each grouping written as a view and then write a SELECT to return only the things you currently need.

It's not likely that I'm going to anticipate all that for every GROUP BY. 😀

Avoiding calculations on predicate columns even though I'm only working on a small number of rows? Yeah... I'll anticipate growth every time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 9

lkennedy76 (3/16/2016)
After a long hard battle of 'I don't wanna', the temp table is the best option in this scenario.
Thank everyone for all your replies. I am truly grateful for this forum in times of getting new gray hair!

Something's wrong then... Like Phil said, the temp table was just an alternative to having a demo table. You don't need a temp table for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lkennedy76 SSChampion Points: 13683 More actions · Answer 10

Jeff Moden (3/16/2016)
lkennedy76 (3/16/2016)
After a long hard battle of 'I don't wanna', the temp table is the best option in this scenario.
Thank everyone for all your replies. I am truly grateful for this forum in times of getting new gray hair!
Something's wrong then... Like Phil said, the temp table was just an alternative to having a demo table. You don't need a temp table for this.

Jeff,

can you expand on 'Demo Table'? It has always been my understanding that @, #, ## are used for temp tables.

https://technet.microsoft.com/en-us/library/ms177399(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx

Thanks! 🙂

MCSE SQL Server 2012\2014\2016

drew.allen SSC Guru Points: 76996 More actions · Answer 11

lkennedy76 (3/17/2016)
Jeff Moden (3/16/2016)
lkennedy76 (3/16/2016)
After a long hard battle of 'I don't wanna', the temp table is the best option in this scenario.
Thank everyone for all your replies. I am truly grateful for this forum in times of getting new gray hair!
Something's wrong then... Like Phil said, the temp table was just an alternative to having a demo table. You don't need a temp table for this.
Jeff,
can you expand on 'Demo Table'? It has always been my understanding that @, #, ## are used for temp tables.
https://technet.microsoft.com/en-us/library/ms177399(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx
Thanks! 🙂

Since we don't have access to your system and can't see what you are seeing, Phil created a table containing sample data for demo purposes only. The idea was that you should be able to replace that table with your actual table(s). Your actual solution shouldn't require creating any kind of temporary table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA