Sum up Null - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Sum up Null

Author

Topic

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-31 : 04:14:38

Hi,
I have a table looks like this

ItemNo PartNo price
1 10 100
1 20 100
1 30 NULL
2 10 150
2 20 100
2 30 100

I did this to find out what is the Total price for Item 1 and 2
[Select ItemNo, sum(price) from Tb group by ItemNo]
That will return
ItemNo price
1 200
2 350
which looks great because SQL server will ignore the NULL price, but this is not what I want. Because if I don't don't look at the saw data then I won't be able to know there is NULL in one of the item. So I may want to achieve
either
ItemNo Price
1 NULL
2 350

OR any mothod that will exclude ItemNo 1

Thanks

**Jonathan**

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 04:18:16

[code]-- prepare sample data
declare @t table (ItemNo int, PartNo int, price int)

insert @t
select 1, 10, 100 union all
select 1, 20, 100 union all
select 1, 30, NULL union all
select 2, 10, 150 union all
select 2, 20, 100 union all
select 2, 30, 100

SELECT ItemNo,
SUM(Price)
FROM @t
GROUP BY ItemNo
HAVING COUNT(*) = COUNT(Price)

SELECT ItemNo,
SUM(Price)
FROM @t
GROUP BY ItemNo
HAVING COUNT(*) <> COUNT(Price)[/code]
Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 04:18:30

"any mothod that will exclude ItemNo 1 "
This maybe?


Select ItemNo, sum(price) 
from Tb AS T
WHERE ItemNo NOT IN
(
    SELECT ItemNo
    FROM Tb
    WHERE PRICE IS NULL
)
group by ItemNo

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 04:23:47

[code]-- prepare sample data
declare @t table (ItemNo int, PartNo int, price int)

insert @t
select 1, 10, 100 union all
select 1, 20, 100 union all
select 1, 30, NULL union all
select 2, 10, 150 union all
select 2, 20, 100 union all
select 2, 30, 100

SELECT ItemNo,
CASE
WHEN COUNT(*) = COUNT(Price) THEN SUM(Price)
ELSE NULL
END
FROM @t
GROUP BY ItemNo[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 04:40:12

Exclude ItemNo = 1


-- prepare sample data
declare @t table (ItemNo int, PartNo int, price int)

insert	@t
select	1, 10, 100 union all
select	1, 20, 100 union all
select	1, 30, NULL union all
select	2, 10, 150 union all
select	2, 20, 100 union all
select	2, 30, 100

select	t.ItemNo, TotalPrice = sum(price)
from	@t as t 
	left join
	(
		select 	ItemNo
		from 	@t
		where	price	is null
	) as n
	on	t.ItemNo	= n.ItemNo
where	n.ItemNo is null
group by t.ItemNo

/*
ItemNo      TotalPrice  
----------- ----------- 
2           350
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 04:43:50

-- Query 1
select t.ItemNo, TotalPrice = sum(price)
from @t as t
left join
(
select ItemNo
from @t
where price is null
) as n
on t.ItemNo = n.ItemNo
where n.ItemNo is null
group by t.ItemNo

/*
ItemNo TotalPrice
----------- -----------
2 350
*/

-- Query 2
select t.ItemNo, TotalPrice = sum(case when n.ItemNo is null then price end)
from @t as t
left join
(
select ItemNo
from @t
where price is null
) as n
on t.ItemNo = n.ItemNo
group by t.ItemNo

/*
ItemNo TotalPrice
----------- -----------
1 NULL
2 350
*/
[/code]

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 05:21:15

So which is the most efficient then?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 05:27:31

Both of mine

With a staggering 25%

-- prepare sample data
declare @t table (ItemNo int, PartNo int, price int)

insert	@t
select	1, 10, 100 union all
select	1, 20, 100 union all
select	1, 30, NULL union all
select	2, 10, 150 union all
select	2, 20, 100 union all
select	2, 30, 100



SELECT		ItemNo,
		SUM(Price)
FROM		@t
GROUP BY	ItemNo
HAVING		COUNT(*) = COUNT(Price)


Select ItemNo, sum(price) 
from @T AS T
WHERE ItemNo NOT IN
(
    SELECT ItemNo
    FROM @t
    WHERE PRICE IS NULL
)
group by ItemNo


select	t.ItemNo, TotalPrice = sum(price)
from	@t as t 
	left join
	(
		select 	ItemNo
		from 	@t
		where	price	is null
	) as n
	on	t.ItemNo	= n.ItemNo
where	n.ItemNo is null
group by t.ItemNo

select	t.ItemNo, TotalPrice = sum(price)
from	@t as t 
	left join
	(
		select 	ItemNo
		from 	@t
		where	price	is null
	) as n
	on	t.ItemNo	= n.ItemNo
where	n.ItemNo is null
group by t.ItemNo














SELECT		ItemNo,
		CASE
			WHEN COUNT(*) = COUNT(Price) THEN SUM(Price)
			ELSE NULL
		END
FROM		@t
GROUP BY	ItemNo




select	t.ItemNo, TotalPrice = sum(case when n.ItemNo is null then price end)
from	@t as t 
	left join
	(
		select 	ItemNo
		from 	@t
		where	price	is null
	) as n
	on	t.ItemNo	= n.ItemNo
group by t.ItemNo

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-31 : 06:35:47

quote:

Originally posted by Peso

-- prepare sample data
declare @t table (ItemNo int, PartNo int, price int)

insert	@t
select	1, 10, 100 union all
select	1, 20, 100 union all
select	1, 30, NULL union all
select	2, 10, 150 union all
select	2, 20, 100 union all
select	2, 30, 100

SELECT		ItemNo,
		CASE
			WHEN COUNT(*) = COUNT(Price) THEN SUM(Price)
			ELSE NULL
		END
FROM		@t
GROUP BY	ItemNo

Peter Larsson
Helsingborg, Sweden

**Jonathan**

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-31 : 06:42:43

quote:

Originally posted by Peso

-- prepare sample data
declare @t table (ItemNo int, PartNo int, price int)

insert	@t
select	1, 10, 100 union all
select	1, 20, 100 union all
select	1, 30, NULL union all
select	2, 10, 150 union all
select	2, 20, 100 union all
select	2, 30, 100

SELECT		ItemNo,
		SUM(Price)
FROM		@t
GROUP BY	ItemNo
HAVING		COUNT(*) = COUNT(Price)

SELECT		ItemNo,
		SUM(Price)
FROM		@t
GROUP BY	ItemNo
HAVING		COUNT(*) <> COUNT(Price)

Peter Larsson
Helsingborg, Sweden

I found this really efficient and clean and it runs well.
But I encounter a new problem when I need to do some logic inside the SUM()
as Followed
SELECT ItemNo,
SUM(CASE..WHEN..THEN price END) as Aprice
FROM @t
GROUP BY ItemNo
HAVING COUNT(*) = COUNT(APrice)

Sorry I didn't expect this to happen

**Jonathan**

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 06:45:13

What is the logic?

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-31 : 07:06:59

SELECT ItemNo,
SUM(CASE price
WHEN price>100 THEN price+10
WHEN price>150 THEN price+20
END) as Aprice
FROM @t
GROUP BY ItemNo
HAVING COUNT(*) = COUNT(APrice)

**Jonathan**

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 07:17:56

(CASE ~~price~~
WHEN price>100 THEN price+10
WHEN price>150 THEN price+20
ELSE price
END)

Try that!

Kristen

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-31 : 07:22:01

Just for information
Some of the things I posted in the forum may not be a complete question, so I am sorry if some of you may think I am wasting other people time by doing so. But the thing I am building in my job sometimes requires me to add in new feature into existing one. For example, they may want to add different profit for different price range, or when there are more suppliers, they may want currency convertion features added in. Things are relatively simple when there are not many items in the inventory, but it grows complicated when there are other factors added in.(such as pricing, currency etc)
So sorry if the problem just grows bigger and bigger and you guy need to re-think the problem.

**Jonathan**

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-31 : 07:26:29

quote:
Originally posted by Kristen

(CASE ~~price~~
WHEN price>100 THEN price+10
WHEN price>150 THEN price+20
ELSE price
END)

Try that!

Kristen

Sorry for the typo
If I am not wrong, the problem lies with I cant use [Count(Aprice )] when it is used [SUM(..) as Aprice] in this way.

**Jonathan**

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 07:33:17

"HAVING COUNT(*) = COUNT(APrice)"

Correct, you can't do that.

But isn't it the same as

HAVING COUNT(*) = COUNT(price)

??

"Some of the things I posted in the forum may not be a complete question, so I am sorry if some of you may think I am wasting other people time by doing so."

Suggest you consider it this way:

People give their time for free here. If you don't ask accurate questions, and thus people waste their time providing answers and then being asked to answer a more accurate question, then the likihood is that they will choose to give their time to other people!

Speaking for myself: rightly or wrongly there are people who post on this forum whos questions I don't bother to read any more ...

Kristen

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-31 : 07:37:25

quote:
Originally posted by Kristen

"HAVING COUNT(*) = COUNT(APrice)"

Correct, you can't do that.

But isn't it the same as

HAVING COUNT(*) = COUNT(price)

??

"Some of the things I posted in the forum may not be a complete question, so I am sorry if some of you may think I am wasting other people time by doing so."

Suggest you consider it this way:

People give their time for free here. If you don't ask accurate questions, and thus people waste their time providing answers and then being asked to answer a more accurate question, then the likihood is that they will choose to give their time to other people!

Speaking for myself: rightly or wrongly there are people who post on this forum whos questions I don't bother to read any more ...

Kristen

Thank you for your advise, to be honest, Peter had some good scolding to me already. I will definitely take the lesson and try to be as accurate as I possibly can.
Thank you.

**Jonathan**

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources