#doubt#

Question

Can we have having clause without group by clause? if yes then when..?

when columns appearing in group by clause may not be in select clause?

Answer 1

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
https://msdn.microsoft.com/en-us/library/ms180199.aspx

Answer 2

a)  If you consider SQL standard , HAVING clause is not allowed without GROUP BY clause  since in the execution flow , HAVING clause condition is imposed on the table group wise.The grouping of records is done based on attributes mentioned in GROUP BY clause.However , according to MySQL standard , it is allowed.Here one record is treated as a GROUP and HAVING clause is imposed hence.

b) Secondly , the columns (or) the attributes mentioned in the GROUP BY clause must be mentioned in SELECT clause along with aggregate functions , if any , like sum() , avg() etc. else it will violate the tabular structure.So yes the attributes mentioned in GROUP BY clause must also be there in SELECT clause.