Doing math across table columns, dealing with NULL

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

Doing math across table columns, dealing with NULL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-20 : 08:03:36

Christine the Pharmacist writes "Please pardon as I am not horribly advanced with SQL...

Using SQL Server 2000, Windows 2000, Service pack 4

I'm trying to figure out marketshares for "preferred" products. I have a table put together by hospital and month of medication use with the totals of each drug dispensed in columns as well.
(example table below)


Hospital     Month     Drug A     Drug B     Drug C     Drug D
    A          7         5           10         58        73
    B          7        NULL         26         98        43
etc.

** Drug C and Drug D are the preferred drugs

I'm getting correct preferred % in all cases except when a value is NULL (result is NULL). I'm using an aggregate (sum), which should ignore NULLs, but it ignores NULLs within a column, and not ignoring across columns. I've tried setting concat_null_yields_null off and I'm still getting NULL (since I'm using the plus sign).

This is my admittedly confused select statement, suspect this is where my problem lies (problems with saying "sum", but using "+" as well?):

select a.Hospital,a.month,convert(float,sum(c.Drug_C+d.Drug_D))/convert(float,sum(a.Drug_A+b.Drug_B+
c.Drug_C+d.Drug_D)) as preferred_share

Thanks for any advice!"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-20 : 08:10:19

Try using IsNull Function

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 08:11:18

Instead of

sum(c.Drug_C+d.Drug_D)

Use

Sum(Case when c.Drug_C is null then 0 else c.Drug_C end)+Sum(Case when c.Drug_D is null then 0 else c.Drug_D end)

Use the similiar to other summations

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-20 : 08:36:41

>>Sum(Case when c.Drug_C is null then 0 else c.Drug_C end)+Sum(Case when c.Drug_D is null then 0 else c.Drug_D end)

Definitely use COALESCE() or ISNULL(), much shorter + easier to read/write!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 08:54:06

>>Definitely use COALESCE() or ISNULL(), much shorter + easier to read/write!

Yes I forget that

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources