Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    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

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    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. #4
    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
  •