SUM and Addition of Null

Question

Post reply

SUM and Addition of Null

iBar

SSCertifiable

Points: 5872
More actions
November 4, 2012 at 1:14 am

#269668

Comments posted to this topic are about the item SUM and Addition of Null

Viewing 15 posts - 1 through 15 (of 19 total)

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 1

🙂 Simply Nice.

(note: aliasing the columns in the first select statement will be enough)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

angad.singh88 Old Hand Points: 300 More actions · Answer 2

Really Nice !

Simple and Best. 🙂

--Angad

(Truth is Higher, Still Higher is Truthful Living)

--Angad Singh
If I Stop Learning, I Cease to Be A SIKH !

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Nice question to start week with +1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Easy one for the Monday 😀

Especially easy because the way the answers were structured, you only had to calculate the last column 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 5

Excellent question, thanks very much. Have a good week.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

Hardy21

SSCrazy Eights

Points: 9708

November 5, 2012 at 2:11 am

#1555916

Nice & Easy one..

Thanks

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 7

Remember that SUM may return NULL, so SUM(num1)+SUM(num2) may lead to NULL:

select

sum(num1) AS S1,

sum(num2) AS S2,

sum(num1) + sum(num2) AS S3,

sum(num1 + num2) AS S4

from

(

select cast('1' as int) as num1, cast(NULL as int) as num2

union all

select '1' as num1, NULL as num2

union all

select '1' as num1, NULL as num2

union all

select NULL as num1, NULL as num2

) as t

The above select return 3,NULL,NULL,NULL

Stuart Davies SSCoach Points: 19505 More actions · Answer 8

Thanks for an easy start to the week

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

good one Carlo

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 10

(Bob Brown)

SSCrazy

Points: 2705

November 5, 2012 at 6:25 am

#1556017

Thanks for the nice start to the week!

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 11

Easy and interesting question.Ta.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Nice and easy question.

Back to basics, really, but quite a few basics: aggregates, aggregates' handling of nulls, arithmetic operations handling of nulls, and implicit type conversion (as well as simple arithemetic) :-).

Tom

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 13

Cheers for something easy on a Monday! :satisfied:

Aigle de Guerre!

kalyani.k478 Default port Points: 1400 More actions · Answer 14

kalyani.k478

Default port

Points: 1400

November 5, 2012 at 9:02 pm

#1556280

easy 🙂 thanks