Re: [SQL] question re. count, group by, and having

[Muralidharan Ramakrishnan]

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 <[EMAIL PROTECTED]> 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.