Re: question re. count, group by, and having

Hi

Logically HAVING is executed after the GROUP BY and it must contain only the columns in the GROUP BY or aggregated function.

select state, count(state) as c from t group by state having c > 5

The above query grouped only on state and HAVING can be used only with the column state or the aggregated function count(state).

Regards,
R.Muralidharan

Rick Schumeyer <rschumeyer(at)ieee(dot)org> wrote:

The following query returns an error (“column c does not exist”) in pg 8.0.3:

(The column ‘state’ is the two letter abbreviation for a US state)

-- get the number of rows for each state; list in descending order; include only states with at least 6 rows

select state, count(state) as c from t group by state having c > 5 order by c desc; -- gives error

If I leave the having clause out, I get the expected results:

select state, count(state) as c from t group by state order by c desc; -- this works

Is this a bug or a feature? I’m not sure why I can use ‘c’ in the order by clause but not the having clause. pg is much happier with the full “having count(state) > 5”. Will this cause count to be evaluated twice?

If it matters, state is varchar(2).

---------------------------------
Yahoo! India Matrimony: Find your partner online.