return null value sum field when no data is available

Question

Post reply

return null value sum field when no data is available

vahid.arr

Ten Centuries

Points: 1063
More actions
May 10, 2012 at 4:05 pm

#261271

this query return null when no data found why?
select
SUM([TedadTarhModiriatTafzili])as TedadTarhModiriatTafzili
, SUM([TedadTarhModiriatJame])as TedadTarhModiriatJame
, SUM([TedadKhodro])as TedadKhodro
, SUM([TedadMotorcycle])as TedadMotorcycle
, SUM([TedadGhayegh])as TedadGhayegh
, SUM([TedadBisim])as TedadBisim
, SUM([TedadDoorbinAksbardari])as TedadDoorbinAksbardari
, SUM([TedadDoorbinCheshmi])as TedadDoorbinCheshmi
FROM [MantagheInfo]
WHERE (YearID = @year)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Because tyou can't do much with null.

What do you want it to return? 0? If so, wrap each SUM in an ISNULL.

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

vahid.arr Ten Centuries Points: 1063 More actions · Answer 2

i want when no data is available (on where statement no data is exist) return nothing not null value

select

SUM(isnull([TedadTarhModiriatTafzili],0))as TedadTarhModiriatTafzili

, SUM(isnull([TedadTarhModiriatJame],0))as TedadTarhModiriatJame

, SUM(isnull([TedadKhodro],0))as TedadKhodro

, SUM(isnull([TedadMotorcycle],0))as TedadMotorcycle

, SUM(isnull([TedadGhayegh],0))as TedadGhayegh

, SUM(isnull([TedadBisim],0))as TedadBisim

, SUM(isnull([TedadDoorbinAksbardari],0))as TedadDoorbinAksbardari

, SUM(isnull([TedadDoorbinCheshmi],0))as TedadDoorbinCheshmi

FROM [MantagheInfo]

WHERE (YearID = @year)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Your code should be changed like below

SELECT ISNULL(SUM([TedadTarhModiriatTafzili]), 0)AS TedadTarhModiriatTafzili ,

ISNULL(SUM([TedadTarhModiriatJame]), 0)AS TedadTarhModiriatJame ,

ISNULL(SUM([TedadKhodro]), 0)AS TedadKhodro ,

ISNULL(SUM([TedadMotorcycle]), 0)AS TedadMotorcycle ,

ISNULL(SUM([TedadGhayegh]), 0)AS TedadGhayegh ,

ISNULL(SUM([TedadBisim]), 0)AS TedadBisim ,

ISNULL(SUM([TedadDoorbinAksbardari]), 0)AS TedadDoorbinAksbardari ,

ISNULL(SUM([TedadDoorbinCheshmi]), 0)AS TedadDoorbinCheshmi

FROM [MantagheInfo]

WHERE ( YearID = @year )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Like this?

if exists

(

select 1

FROM [MantagheInfo]

WHERE (YearID = @year)

)

select

SUM(isnull([TedadTarhModiriatTafzili],0))as TedadTarhModiriatTafzili

, SUM(isnull([TedadTarhModiriatJame],0))as TedadTarhModiriatJame

, SUM(isnull([TedadKhodro],0))as TedadKhodro

, SUM(isnull([TedadMotorcycle],0))as TedadMotorcycle

, SUM(isnull([TedadGhayegh],0))as TedadGhayegh

, SUM(isnull([TedadBisim],0))as TedadBisim

, SUM(isnull([TedadDoorbinAksbardari],0))as TedadDoorbinAksbardari

, SUM(isnull([TedadDoorbinCheshmi],0))as TedadDoorbinCheshmi

FROM [MantagheInfo]

WHERE (YearID = @year)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

vahid.arr (5/10/2012)
i want when no data is available (on where statement no data is exist) return nothing not null value
select
SUM(isnull([TedadTarhModiriatTafzili],0))as TedadTarhModiriatTafzili
, SUM(isnull([TedadTarhModiriatJame],0))as TedadTarhModiriatJame
, SUM(isnull([TedadKhodro],0))as TedadKhodro
, SUM(isnull([TedadMotorcycle],0))as TedadMotorcycle
, SUM(isnull([TedadGhayegh],0))as TedadGhayegh
, SUM(isnull([TedadBisim],0))as TedadBisim
, SUM(isnull([TedadDoorbinAksbardari],0))as TedadDoorbinAksbardari
, SUM(isnull([TedadDoorbinCheshmi],0))as TedadDoorbinCheshmi
FROM [MantagheInfo]
WHERE (YearID = @year)

By "Nothing" do you mean blank or 0?

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

I think the OP wants no result set at all when there is no row. 0 row(s) returned.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/