CODEWITHSUNDEEP
sqloracle-databasehive
Saras
Saras

Reputation: 43

group by with condition on count(*)

I am trying to find the count of obsv_dt which has less than million records

 select obsv_dt,count(*) as c from table
 group by obsv_dt 
 having c<1000000
 order by c

is giving unable to resolve column 'c'. I get that 'c' is alias and this error is expected How can i get this working?

Upvotes: 0

Views: 43

Answers (3)

user3315556
user3315556

Reputation: 175

Below is the precedence of sql clauses:

  1. FROM clause
  2. ON clause
  3. OUTER clause
  4. WHERE clause
  5. GROUP BY clause
  6. HAVING clause
  7. SELECT clause
  8. DISTINCT clause
  9. ORDER BY clause
  10. TOP clause

Since HAVING clause is evaluated prior to the SELECT clause it is unware of the aliases. Similarly for all clauses except for ORDER BY where we can include the alias for sorting the result set.

Upvotes: 0

wp78de
wp78de

Reputation: 18950

You cannot use the alias before it has been calculated; try:

select
  obsv_dt,
  count(obsv_dt) as c
from
  table
group by obsv_dt
having count(obsv_dt) < 1000000
order by count(obsv_dt)

There is a subtle difference in using count(*) vs count(col). But often it does not matter. count(*) vs count(column-name) - which is more correct?

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

 select obsv_dt,count(*) as c from table
 group by obsv_dt 
 having count(*) <1000000
 order by count(*)

Upvotes: 1

Related Questions