help with select / sum / nulls

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

help with select / sum / nulls

Author

Topic

BioMash
Starting Member

8 Posts

Posted - 2006-11-11 : 22:34:33

i have the following select statement, the balance due column allows nulls and I am trying to eliminate all rows where total or credit is null. Is the only way to run the two select statements again in the where clause?

select
c.CustomerID ConsultantID,
c.CustomerFullName CustomerFullName,
sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID) as Total,
sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID and TransactionTypeID = 'Credit Memo') as Credit

from
Customer c

order by ConsultantID

----------------

The following gives me what I want but I am sure there must be a better way, any pointers would be appreciated:)

select
c.CustomerID ConsultantID,
c.CustomerFullName CustomerFullName,
ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID), 0) - as Total,
ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID and TransactionTypeID = 'Credit Memo'), 0) as Credit

from
Customer c
where (ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID), 0) <> 0
or
ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID and TransactionTypeID = 'Credit Memo'), 0) <> 0)

order by ConsultantID
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-11 : 23:22:06

[code]select
c.CustomerID ConsultantID,
c.CustomerFullName CustomerFullName,
IsNull(sum(IsNull(i.BalanceDue, 0)),0) as Total,
IsNull(sum( case when TransactionTypeID = 'Credit Memo' then IsNull(i.BalanceDue,0) else 0 end),0) as Credit
from
Customer c
Join
Invoice i
on i.CustomerID = c.CustomerID
Group By c.CustomerID,
c.CustomerFullName
order by ConsultantID[/code]

EDIT: OOPS!! I forgot GROUP BY...thanks Peter!

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-12 : 04:05:34

quote:
Originally posted by BioMash

The following gives me what I want but I am sure there must be a better way, any pointers would be appreciated:)

No way this query gives you what you want. It would throw a syntax error because of the minus sign before "as Total".

select		c.CustomerID ConsultantID,
		c.CustomerFullName CustomerFullName,
		ISNULL(sum(i.BalanceDue), 0) as Total,
		ISNULL(sum(case when TransactionTypeID = 'Credit Memo' then i.BalanceDue end), 0) as Credit
from		Customer c
inner join	Invoice i on i.CustomerID = c.CustomerID
group by	c.CustomerID,
		c.CustomerFullName
having		ISNULL(sum(i.BalanceDue), 0) <> 0
		or ISNULL(sum(case when TransactionTypeID = 'Credit Memo' then i.BalanceDue end), 0) <> 0
order by	c.CustomerID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources