0 or $0.00 instead of NULL?

Question

Post reply

0 or $0.00 instead of NULL?

chef423

SSCertifiable

Points: 5279
More actions
November 17, 2017 at 10:02 am

#329614

SELECT '$' + CONVERT(VARCHAR(12), SUM(CAST([ie].[AMOUNT] AS MONEY)))
I tried this:
SELECT '$' + CONVERT(VARCHAR(12), SUM(CAST(ISNULL([ie].[AMOUNT],0) AS MONEY)))
It executes but I still get NULL.
Usually the output is some dollar amount: $55.22
But when no data is present id like to get 0 or better yet, $0.00
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Thom A SSC Guru Points: 99129 More actions · Answer 1

Wrap the ISNULL around your SUM instead.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

Thom A - Friday, November 17, 2017 10:05 AM
Wrap the ISNULL around your SUM instead.

Or even add another:
SELECT '$' + ISNULL(CONVERT(VARCHAR(12), SUM(CAST(ISNULL([ie].[AMOUNT],0) AS MONEY))),'0.00')

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

chef423 SSCertifiable Points: 5279 More actions · Answer 3

Thom A - Friday, November 17, 2017 10:05 AM
Wrap the ISNULL around your SUM instead.

SELECT '$' + CONVERT(VARCHAR(12), ISNULL(SUM(CAST([ie].[AMOUNT] AS MONEY)),0))

Boom

Thanks

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

Yeah, just to clarify, NULLs are ignored by SUM anyway, so wrapping the column values being summed in an ISNULL is redundant (EDIT: as pointed out below, not completely true); you'll only get a NULL output from the SUM function when the rowset over which the SUM is being computed is empty or all the rows have NULLs for the column/expression being summed.

That's why to do what you want you have to pass the output of the SUM to ISNULL.

Hopefully that helps 🙂

EDIT: Cleaned up some of the issues pointed out by Phil and Thom below. Thanks guys 🙂

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

Jacob Wilkins - Friday, November 17, 2017 10:42 AM
Yeah, just to clarify, NULLs are ignored by SUM anyway, so wrapping the column values being summed in an ISNULL is redundant;

I disagree, slightly.
If NULLs are included in a SUM(), a warning is output: NULL values eliminated ..., putting the ISNULL inside the brackets avoids the generation of this warning.

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

I'm aware of that; they are ignored in computing the sum, and the warning only appears if your ANSI_WARNINGS is ON, so the bit about the warning's not even always true 🙂

Either way, if we want to say that "eliminated" is a better word than "ignored", or that it would have been even better for me to have said "ignored in computing the sum" as I did here or something similar like "NULL column values do not affect the value output from the SUM function", I'll not contest the point 🙂

Thom A SSC Guru Points: 99129 More actions · Answer 7

It's also worth noting, however that the SUM of a "bunch" of NULL values is NULL, where as for COUNT the value would be 0. Similarly, the SUM of now rows is NULL, and for COUNT 0. This is why, for SUM, if you expect a 0 for no rows (or that any that do return have the value NULL) you need to wrap the ISNULL around the SUM, not inside it.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

Thom A - Friday, November 17, 2017 10:58 AM
It's also worth nothing, however that the SUM of a "bunch" of NULL values is NULL, where as for COUNT the value would be 0. Similarly, the SUM of now rows is NULL, and for COUNT 0. This is why, for SUM, if you expect a 0 for no rows (or that any that do return have the value NULL) you need to wrap the ISNULL around the SUM, not inside it.

Yeah, one of the reasons I wouldn't contest that "eliminate" is maybe a better word. It maybe makes it a bit clearer that having all NULLs being summed yield the same output as summing over an empty rowset.

It also nicely points out the one case in which wrapping the column value in ISNULL(column, 0) would make a functional difference.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 9

Jacob Wilkins - Friday, November 17, 2017 10:51 AM
I'm aware of that; they are ignored in computing the sum, and the warning only appears if your ANSI_WARNINGS is ON, so the bit about the warning's not even always true 🙂
Either way, if we want to say that "eliminated" is a better word than "ignored", or that it would have been even better for me to have said "ignored in computing the sum" as I did here or something similar like "NULL column values do not affect the value output from the SUM function", I'll not contest the point 🙂

No, actually my comment was aimed at your use of the word 'redundant'. But I think we're all bored with this topic now, so I'll leave it alone. Happy Friday 🙂

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

chef423 - Friday, November 17, 2017 10:22 AM
Thom A - Friday, November 17, 2017 10:05 AM
Wrap the ISNULL around your SUM instead.
SELECT '$' + CONVERT(VARCHAR(12), ISNULL(SUM(CAST([ie].[AMOUNT] AS MONEY)),0))
Boom
Thanks

You can make it a bit faster and solve the ISNULL problem at the same time. Right now, you're casting EVERY single value to MONEY and then SUMming it. Instead, do an ISNULL on the column, SUM it, and the convert ONLY the Sum. It would also be handy to be consistent on your conversions for this. Either settle on the use of CAST or the use of CONVERT. Just to drive portability purists nuts and for easier readability, my personal preference is to use CONVERT.

Like this...

SELECT '$' + CONVERT(VARCHAR(12),CONVERT(MONEY,SUM(ISNULL(ie.AMOUNT,0))))

Most will agree that formatting of data should be done in the front end (if there is one) but, sometimes you gotta do what you gotta do. If you also want thousands separators in the output, use the following.

SELECT '$' + CONVERT(VARCHAR(16),CONVERT(MONEY,SUM(ISNULL(ie.AMOUNT,0))),1)

--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)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 11

Jeff Moden - Friday, November 17, 2017 5:47 PM
... If you also want thousands separators in the output, use the following.

SELECT '$' + CONVERT(VARCHAR(16),CONVERT(MONEY,SUM(ISNULL(ie.AMOUNT,0))),1)

Jeff... Very nice tip!
I think 99.99% of topics, where the proposed solution uses the CONVERT function's style parameter, revolve around date & time conversions.
I'm happy to see it being used in used in a completely different context... It's also a nice (and important) reminder that the BOL doesn't end at the bottom of the Date and Time Styles chart.
Thank you for that. 🙂

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

Jason A. Long - Friday, November 17, 2017 8:02 PM
Jeff Moden - Friday, November 17, 2017 5:47 PM
... If you also want thousands separators in the output, use the following.

SELECT '$' + CONVERT(VARCHAR(16),CONVERT(MONEY,SUM(ISNULL(ie.AMOUNT,0))),1)
Jeff... Very nice tip!
I think 99.99% of topics, where the proposed solution uses the CONVERT function's style parameter, revolve around date & time conversions.
I'm happy to see it being used in used in a completely different context... It's also a nice (and important) reminder that the BOL doesn't end at the bottom of the Date and Time Styles chart.
Thank you for that. 🙂

You bet, Jason. Thank you for the very kind feedback. And thanks for posting the BOL link on the subject.

--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)