group by with condition on count(*)

Question

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?

`having count(*) <1000000`. (The SELECT list column alias c hasn't been defined when HAVING is evaluated.) However, c can be used in the ORDER BY. — jarlh, Nov 18 '20 at 20:34

score 1 · Answer 1 · answered Nov 18 '20 at 20:35

1

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

answered Nov 18 '20 at 20:35

OldProgrammer

12,050
4
24
45

wp78de · Answer 2 · 2020-11-18T20:40:46.070

1

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?

edited Nov 18 '20 at 20:40

answered Nov 18 '20 at 20:35

wp78de

18,207
7
43
71

score 0 · Answer 3 · answered Nov 18 '20 at 20:43

Below is the precedence of sql clauses:

FROM clause

ON clause

OUTER clause

WHERE clause

GROUP BY clause

HAVING clause

SELECT clause

DISTINCT clause

ORDER BY clause

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.

group by with condition on count(*)

3 Answers3