Referring to an aliased aggregate function in where?

[SyntaxTerror]

If, in my select, I have something like

SUM(Quantity) as TotalQty

...Then how do I refer to that in the where clause?

I want to say...

WHERE TotalQty IS NOT NULL

...but it doesn't recognize the alias, so I try...

WHERE sum(Quantity) IS NOT NULL

...but it won't take that either.

Workarounds?

 

[RiverGuy]

use HAVING for aggregates, not the WHERE clause.

 

[PWise]

having sum(Quantity)>0

or
having isnull(sum(Quantity),0)>0
or
WHERE Quantity IS NOT NULL

 

[gmmastros]

Because it is an aggregate, you need to use a having clause.

Ex:

Select ItemNumber,
       Sum(Quantity) As TotalQty
From   SomeTable
Where  ShowSize > 7
Group By ItemNumber
Having Sum(Quantity) Is Not NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

depending on what you want to achive

 

[SyntaxTerror]

Oddly, when using HAVING, it's still producing null values...

Ah well. It's just a subquery. I'll filter out the NULLs when I reference it later.

Thanks all.

 

[markros]

Are you using a LEFT JOIN in your query?

PluralSight Learning Library