# Thread: Priority of evaluating HAVING clause

1. Registered User
Join Date
Jun 2005
Location
Waukesha WI
Posts
78

## Unanswered: Priority of evaluating HAVING clause

I have a view that has the following general sql:

SELECT STUFF,
Count(Something) AS CountOfsomething,
Avg(1-(ACost/APrice)) AS Discount,
FROM Somewhere INNER JOIN Sometable ON Field = someValue
INNER JOIN AnotherTable ON Field1 = someOtherValue
GROUP BY field3, ACost, APrice,
HAVING (((Field4)=32)
AND (ACost > 0)
And (APrice > 0))
ORDER BY Something

I wish to avoid anywhere the Numerator or denominator in the Average calc is zero. Problem is that I still get an error - it appears that the HAVING clause is evaluated AFTER the calculation.

If this is correct, does anyone know of a way to accomplish what I want to see in the view?

2. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

Code:
```SELECT STUFF,
Count(Something) AS CountOfsomething,
Avg(1-(ACost/APrice)) AS Discount,
FROM Somewhere INNER JOIN Sometable ON Field = someValue
INNER JOIN AnotherTable ON Field1 = someOtherValue
WHERE (ACost > 0)
And (APrice > 0)
And (Field4)=32)
GROUP BY field3, ACost, APrice
ORDER BY Something```

3. Registered User
Join Date
Jan 2005
Location
Green Bay
Posts
201
If aprice and acost are in you table use a where clause prior to the group by

if it is sum(price) as aprice you will need to do this is a sub select

select stuff from (
select
sum(price) aprice
)
where aprice > 0
group by stuff
having other conditions

4. Registered User
Join Date
Dec 2003
Posts
1,074
The rule we follow is that you only reference the aggregate fields in your SELECT clause in your HAVING clause, so something like the following would be appropriate:

Code:
```HAVING Count(Something) > 1 AND
Avg(1-(ACost/APrice)) > 0```
-cf

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•