Sum for one to many, change NULL to zero in SQL.

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

SQL Server 2000 Forums

Transact-SQL (2000)

Sum for one to many, change NULL to zero in SQL.

Author

Topic

danyeung
Posting Yak Master

102 Posts

Posted - 2006-09-12 : 13:36:05

Here is the SQL statement:

select Distributor.Name, sum(AllocationFund.Amount) as Allocaiton,
round(sum(case Request.Amount when NULL then 0 else Request.Amount end), 0) as RequestAmount,
round(sum(AllocationFund.Amount) - sum(Request.Amount), 0) as Balance
from Distributor inner join AllocationFund
on Distributor.DistributorID = AllocationFund.DistributorID
left outer join Request
on AllocationFund.AllocTypeID = Request.AllocTypeID
and Distributor.DistributorID = Request.distributorID
group by Distributor.Name
order by Distributor.Name

Data for Distributor1 (Result(1))
Allocation Fund 1 = 45000
Allocation Fund 2 = 3000
Allocation Fund 3 = 1875

Request Amount for Fund 1 = 684.76
Request Amount for Fund 1 = 29600
Request Amount for Fund 2 = 1984.85
Request Amount for Fund 2 = 11998.52
Request Amount for Fund 2 = 444.58
Request Amount for Fund 2 = 375.14

The Result(1) should be: Name = Distributor1
Allocation = 49875 (45000 + 3000 + 1875)
RequestAmount = 45088 (684.76 + 29600 + 1984.85 + 11998.52 + 444.58 + 375.14)
Balance = 4787 (49875 - 45088)

But I got the following result:
Result(1): Name = Distributor1
Allocation = 103875 (45000 * 2 + 3000 * 4 + 1875)
RequestAmount = 45090.0000
Balance = 58790.0000

Result(2): Name = Distributor2
Allocation = 2000
RequestAmount = NULL
Balance = NULL

Here are my questions:
(1) How can I get the correct result for Result(1)?
(2) How do I change the NULL to zero in the SQL statement for Result(2)?
(3) How do I round the Request Amount to zero decimal? The Request Amount type in the table is money.

Thanks.
DanYeung

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-12 : 13:41:25

Can you post the table structure. so It can be better understood

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-12 : 13:45:33

1)
You are using the "simple CASE" rather than the "searched CASE". (see CASE in Books Online)
If you want to use the simple case on a nullable column then you would need isNull(<col>, 0). For a searched CASE you can just say: case when <col> is null then...

2) See IsNull in Books Online

3) see Round in Books Online

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-12 : 14:05:52

select Distributor.Name, sum(AllocationFund.Amount) as Allocation,
(SELECT CAST(round(sum(ISNULL(Request.Amount, 0)), 0) AS int) FROM Request WHERE AllocationFund.AllocTypeID = Request.AllocTypeID
and Distributor.DistributorID = Request.distributorID) as RequestAmount,
sum(AllocationFund.Amount) - (SELECT CAST(round(sum(ISNULL(Request.Amount, 0)), 0) AS int) FROM Request WHERE AllocationFund.AllocTypeID = Request.AllocTypeID
and Distributor.DistributorID = Request.distributorID) as Balance
from Distributor inner join AllocationFund
on Distributor.DistributorID = AllocationFund.DistributorID
group by Distributor.Name
order by Distributor.Name

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-12 : 14:13:19

This will not work, because NULL never equals NULL

case Request.Amount when NULL then 0 else Request.Amount end

CODO ERGO SUM

danyeung
Posting Yak Master

102 Posts

Posted - 2006-09-12 : 14:51:35

Okay, let me work on the NULL issue. Any help on summing correctly to get the result Allocation The Result(1) should be:
Name = Distributor1
Allocation = 49875 (45000 + 3000 + 1875)
RequestAmount = 45088 (684.76 + 29600 + 1984.85 + 11998.52 + 444.58 + 375.14)
Balance = 4787 (49875 - 45088)

I tried snSQL's suggestion. The Allocation came out correct, but the RequestAmount and Balance were summing the whole tabel.

Thanks.
DanYeung

Kristen
Test

22859 Posts

Posted - 2006-09-12 : 14:55:39

"This will not work, because NULL never equals NULL"

SET ANSI_NULLS God Willing!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-12 : 15:47:12

quote:
Originally posted by Kristen

"This will not work, because NULL never equals NULL"

SET ANSI_NULLS God Willing!

Are you trying to confuse the issue?

CODO ERGO SUM

danyeung
Posting Yak Master

102 Posts

Posted - 2006-09-12 : 16:14:07

Michael Valentine Jones and Kristen,
If case Request.Amount when NULL then 0 else Request.Amount end does not work and NULL never equal to NULL, what is your suggestion? Thanks.

DanYeung

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-12 : 16:17:38

When you say the request is summing the whole table - then your data must have a problem because the code I gave you is selecting only the Requests for the current AllocationFund. Do you maybe need to add in a further filter like a date into the Request subquery WHERE clause? That wasn't in your original post so I couldn't guess at what you want?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-12 : 16:22:52

quote:
Originally posted by danyeung

Michael Valentine Jones and Kristen,
If case Request.Amount when NULL then 0 else Request.Amount end does not work and NULL never equal to NULL, what is your suggestion? Thanks.

DanYeung

As other people already suggested, you can use the isnull function.

You can also use the coalesce function.

CODO ERGO SUM

danyeung
Posting Yak Master

102 Posts

Posted - 2006-09-12 : 16:26:54

Okay, I got it. Here it goes:
case isNULL(tblSPFR_Request.mnyAmount, 0) when 0 then 0 else tblSPFR_Request.mnyAmount end

Anyone have idea about the summing issue?
Also the rounding works correctly, but I don't know how to get rid of the .0000.

Thanks
DanYeung

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 16:37:50

If using ISNULL function as above, there is no need to use CASE statement.
A simple SUM(ISNULL(tblSPFR_Request.mnyAmount, 0)) will do.

select		Distributor.Name,
		sum(AllocationFund.Amount) as Allocaiton, 
		round(sum(isnull(Request.Amount, 0))) as RequestAmount,
		round(sum(AllocationFund.Amount) - sum(isnull(Request.Amount, 0)) as Balance
from		Distributor
inner join	AllocationFund on Distributor.DistributorID = AllocationFund.DistributorID
left join	Request on AllocationFund.AllocTypeID = Request.AllocTypeID and Distributor.DistributorID = Request.distributorID
group by	Distributor.Name
order by	Distributor.Name

Peter Larsson
Helsingborg, Sweden

danyeung
Posting Yak Master

102 Posts

Posted - 2006-09-12 : 16:40:13

Thanks. I changed it.
DanYeung

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-12 : 16:57:37