Where on a 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

SQL Server 2008 Forums

Transact-SQL (2008)

Where on a Sum

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-27 : 11:16:25

How do I add a where statement to this:
I think something like where New_Orders > 0
Just not sure how to do it

sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.price) as New_Orders,

Thanks

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 11:29:14

Should be something like this:


SELECT sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.price) as New_Orders
FROM MyTable
WHERE New_Orders > 0
HAVING sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.price) > 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 11:48:09

quote:

Originally posted by Kristen

Should be something like this:


SELECT other columns...
FROM
(
SELECT sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.price) as New_Orders,other columns...
FROM MyTable
)t
WHERE New_Orders > 0

Not exactly. A small tweak required

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 11:56:19

Doh ... didn't read the ALIAS name of the SELECT, thanks.

I'll go back and rework my answer. HAVING would do, wouldn't it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 11:59:28

quote:
Originally posted by Kristen

Doh ... didn't read the ALIAS name of the SELECT, thanks.

I'll go back and rework my answer. HAVING would do, wouldn't it?

yup would do

but i guess OP does have some other columns to return also so may be he needs a group by too

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-27 : 13:07:13

OK great, not how would I add a where co.stat = 'o' Can you comined a having and where?

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 14:15:24

"Can you comined a having and where?"

Yes, or you can just put them in the HAVING - Query optimiser is smart enough to work out if the condition could be done in the WHERE instead.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 00:45:44

quote:
Originally posted by baze7

OK great, not how would I add a where co.stat = 'o' Can you comined a having and where?

Post the full query with all questions

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources