SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SUM

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-09-10 : 02:19:08

HI

When I tried to get the sum I get the result NULL. What's the problem?

CASE WHEN CO_prepare = 'No' THEN 1.0 ELSE (CASE WHEN CO_prepare = 'Yes' THEN 0 ELSE NULL END)
END + CASE WHEN CO_loyal = 'No' THEN 1.0 ELSE (CASE WHEN CO_loyal = 'Yes' THEN 0 ELSE NULL END)
END + CASE WHEN CO_identy = 'No' THEN 1.0 ELSE (CASE WHEN CO_identy = 'Yes' THEN 0 ELSE NULL END)
END + CASE WHEN CO_maker = 'No' THEN 1.0 ELSE (CASE WHEN CO_maker = 'Yes' THEN 0 ELSE NULL END) END AS SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 02:21:13

one of the CASE . . END returns NULL

KH
[spoiler]Time is always against us[/spoiler]

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-09-10 : 02:23:20

yes is there a way that it will ignore that then it will add?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 02:25:02

use ISNULL() or COALESCE()

KH
[spoiler]Time is always against us[/spoiler]

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-09-10 : 02:26:15

i have thought of that but it will not get the correct result but zero

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-09-10 : 02:29:48

Here's my statement:

CASE WHEN CO_prepare = 'No' THEN 1.0 ELSE (CASE WHEN CO_prepare = 'Yes' THEN 0 ELSE NULL END) END AS CO_prepare,
CASE WHEN CO_loyal = 'No' THEN 1.0 ELSE (CASE WHEN CO_loyal = 'Yes' THEN 0 ELSE NULL END) END AS CO_loyal,
CASE WHEN CO_identy = 'No' THEN 1.0 ELSE (CASE WHEN CO_identy = 'Yes' THEN 0 ELSE NULL END) END AS CO_identy,
CASE WHEN CO_maker = 'No' THEN 1.0 ELSE (CASE WHEN CO_maker = 'Yes' THEN 0 ELSE NULL END) END AS CO_maker

What I want to get is the average but it will excludes NULL results.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 02:30:27

then what do you want to do ?

take a look at one of your case statement here. when CO_loyal = 'No' or 'Yes' you will return 0. Any other value it will return NULL. So what do you want to do when CO_loyal is not No and Yes ? ?


CASE WHEN 
          CO_loyal = 'No' 
     THEN 
          1.0 
     ELSE 
          (CASE WHEN 
                     CO_loyal = 'Yes' 
                THEN 
                     0 
                ELSE 
                     NULL 
                END) 
      END

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 02:48:20

quote:
Originally posted by BankOfficerHere

Here's my statement:

CASE WHEN CO_prepare = 'No' THEN 1.0 ELSE (CASE WHEN CO_prepare = 'Yes' THEN 0 ELSE NULL END) END AS CO_prepare,
CASE WHEN CO_loyal = 'No' THEN 1.0 ELSE (CASE WHEN CO_loyal = 'Yes' THEN 0 ELSE NULL END) END AS CO_loyal,
CASE WHEN CO_identy = 'No' THEN 1.0 ELSE (CASE WHEN CO_identy = 'Yes' THEN 0 ELSE NULL END) END AS CO_identy,
CASE WHEN CO_maker = 'No' THEN 1.0 ELSE (CASE WHEN CO_maker = 'Yes' THEN 0 ELSE NULL END) END AS CO_maker

What I want to get is the average but it will excludes NULL results.

Average of what ? How do you calculate that ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 03:48:04

If you just add them together, you will get a NULL result.
If you make an AVERAGE them, NULL values are not counted.
We have been over this many times before...

AVG(CASE CO_prepare
	WHEN 'No' THEN 1.0 
	WHEN 'Yes' THEN 0.0
	ELSE NULL
END) AS CO_prepare, 
AVG(CASE CO_loyal
	WHEN 'No' THEN 1.0
	WHEN 'Yes' THEN 0.0
	ELSE NULL
END) AS CO_loyal, 
AVG(CASE CO_identy
	WHEN 'No' THEN 1.0
	WHEN 'Yes' THEN 0.0
	ELSE NULL
END) AS CO_identy,
AVG(CASE CO_maker
	WHEN 'No' THEN 1.0
	WHEN 'Yes' THEN 0.0
	ELSE NULL
END) AS CO_maker

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources