Can i use string type with having clause(Like ....having sex='male')

Question

select country,count() from employee group by country having id<4; this query works fine but when i use select country,count() from employee group by country having sex='male'; showing error :-ORA-00979: not a GROUP BY expression Why??

Answer 1

the firts should work because there is the same aggregation level between country and id (each country have one id only)

the second don't work because sex is not an aggregated column and have not the same aggregation level so

you should use where for filter if you don't need sex in aggregation clause

  select country,count(*) from employee 
  where  sex='male'
  group by country;

or you could add sex to the group by and filter

  select country,sex,  count(*) from employee 
  group by country, sex
  having sez='male';