March 15, 2016 at 7:55 am
okay guys, I am stuck. I have two columns that I need to add and make one total for a report. there are null values so I have to add in ISNULL but only the first column (Amount) is adding to one total. The second is listing all amounts, not totaling. query below.
select
ISNULL(Amount, 0) + ISNULL(OrgAmount, 0) as TotalAmount
from
payments
where staus = 1 and date = '03/15/2016'
group by Amount, OrgAmount
I have used SUM it still only add the first column (Amount)
MCSE SQL Server 2012\2014\2016
March 15, 2016 at 8:07 am
lkennedy76 (3/15/2016)
okay guys, I am stuck. I have two columns that I need to add and make one total for a report. there are null values so I have to add in ISNULL but only the first column (Amount) is adding to one total. The second is listing all amounts, not totaling. query below.select
ISNULL(Amount, 0) + ISNULL(OrgAmount, 0) as TotalAmount
from
payments
where staus = 1 and date = '03/15/2016'
group by Amount, OrgAmount
I have used SUM it still only add the first column (Amount)
That query looks a bit off, because you are grouping on the columns which you are summing.
I would have expected something more like this:
SELECT AccountId
, TotalAmount = SUM(ISNULL(Amount, 0) + ISNULL(OrgAmount, 0))
FROM payments
WHERE status = 1 AND date = '20160315'
GROUP BY AccountId
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 15, 2016 at 8:07 am
You don't need ISNULL.
Why are you grouping by the things you want to add? Group By should be what you want the totals summed by. If you just want a single total, you don't need a group by.
select SUM(Amount) + SUM(OrgAmount) as TotalAmount
from payments
where staus = 1 and date = '2016-03-15' -- the other date format is ambiguous
Edit: Fixed
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2016 at 8:14 am
lkennedy76 (3/15/2016)
okay guys, I am stuck. I have two columns that I need to add and make one total for a report. there are null values so I have to add in ISNULL but only the first column (Amount) is adding to one total. The second is listing all amounts, not totaling. query below.select
ISNULL(Amount, 0) + ISNULL(OrgAmount, 0) as TotalAmount
from
payments
where staus = 1 and date = '03/15/2016'
group by Amount, OrgAmount
I have used SUM it still only add the first column (Amount)
I suspect your GROUP BY was from when you tried it using SUM. As such it makes it confusing...that being said for simplicity here is a simple example excluding your other columns.
DECLARE @myTable TABLE (Amount INT, OrgAmount INT)
INSERT INTO @myTable
VALUES (50, 100), (10, NULL), (NULL, 40), (120, 30)
SELECT
SUM(Amount) + SUM(OrgAmount) AS TotalAmount
FROM
@myTable
March 15, 2016 at 8:20 am
GilaMonster (3/15/2016)
You don't need ISNULL.Why are you grouping by the things you want to add? Group By should be what you want the totals summed by. If you just want a single total, you don't need a group by.
select SUM(Amount + OrgAmount) as TotalAmount
from payments
where staus = 1 and date = '2016-03-15' -- the other date format is ambiguous
Gail, if you do it this way you'll end up losing values that are added with NULL's. I think that is why the OP was originally wanting to use ISNULL to avoid this, but you are right they are not required.
DECLARE @myTable TABLE (Amount INT, OrgAmount INT)
INSERT INTO @myTable
VALUES (50, 100), (10, NULL), (NULL, 40), (120, 30)
--SELECT
--SUM(Amount) + SUM(OrgAmount) AS TotalAmount
--FROM
--@myTable
SELECT
SUM(Amount + OrgAmount) AS TotalAmount
FROM
@myTable
March 15, 2016 at 8:26 am
@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
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 15, 2016 at 8:35 am
Jason-299789 (3/15/2016)
@yb751The 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
Hi Jason, good point. I wasn't taking the aggregate into account. Well depending on what the OP's desired result is here is a few examples.
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
--VS
SELECT
Account , SUM(Amount + OrgAmount) AS TotalAmount
FROM
@myTable
group by Account
--VS
SELECT
Account , SUM(ISNULL(Amount, 0)) + SUM(ISNULL(OrgAmount,0)) AS TotalAmount
FROM
@myTable
group by Account
March 15, 2016 at 8:53 am
I have null values that I need to remove, ISNULL does that, why would I not use it?
MCSE SQL Server 2012\2014\2016
March 15, 2016 at 8:57 am
yb751 (3/15/2016)
Jason-299789 (3/15/2016)
@yb751The 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
Hi Jason, good point. I wasn't taking the aggregate into account. Well depending on what the OP's desired result is here is a few examples.
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
--VS
SELECT
Account , SUM(Amount + OrgAmount) AS TotalAmount
FROM
@myTable
group by Account
--VS
SELECT
Account , SUM(ISNULL(Amount, 0)) + SUM(ISNULL(OrgAmount,0)) AS TotalAmount
FROM
@myTable
group by Account
Thanks guys, I don't want to create a temp table, I am trying to avoid that. Let me try all these to to see if they work...
MCSE SQL Server 2012\2014\2016
March 15, 2016 at 8:59 am
lkennedy76 (3/15/2016)
I have null values that I need to remove, ISNULL does that, why would I not use it?
Because it some cases it was redundant. We didn't have enough information or data samples to come up with the best solution. See (and try) some of the examples given by the different posters. Use what best suits your needs.
Thanks guys, I don't want to create a temp table, I am trying to avoid that. Let me try all these to to see if they work...
The temp table was strictly for demonstration purposes.
March 15, 2016 at 9:02 am
@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.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 15, 2016 at 9:03 am
Phil Parkin (3/15/2016)
lkennedy76 (3/15/2016)
okay guys, I am stuck. I have two columns that I need to add and make one total for a report. there are null values so I have to add in ISNULL but only the first column (Amount) is adding to one total. The second is listing all amounts, not totaling. query below.select
ISNULL(Amount, 0) + ISNULL(OrgAmount, 0) as TotalAmount
from
payments
where staus = 1 and date = '03/15/2016'
group by Amount, OrgAmount
I have used SUM it still only add the first column (Amount)
That query looks a bit off, because you are grouping on the columns which you are summing.
I would have expected something more like this:
SELECT AccountId, TotalAmount = SUM(ISNULL(Amount, 0) + ISNULL(OrgAmount, 0))
FROM payments
WHERE status = 1 AND date = '20160315'
GROUP BY AccountId
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
MCSE SQL Server 2012\2014\2016
March 15, 2016 at 9:15 am
Jason-299789 (3/15/2016)
@yb751The 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 15, 2016 at 9:19 am
Phil Parkin (3/15/2016)
Jason-299789 (3/15/2016)
@yb751The 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.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 15, 2016 at 9:33 am
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.
MCSE SQL Server 2012\2014\2016
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply