12.4. Other Tricks with HAVING Clauses
You can use the aggregate functions and the HAVING clause to determine certain characteristics of the groups formed by the GROUP BY clause. For example, given a simple grouped table, you can determine the following properties of the groups with these HAVING clauses:
HAVING COUNT (DISTINCT col_x) = COUNT (col_x)— col_x has all distinct values. HAVING COUNT(*) = COUNT (col_x);—There are no NULLS in the column. HAVING MIN (col_x - <const>) = -MAX (col_x - <const>)— col_x deviates above and below const by the same amount. HAVING MIN (col_x) * MAX (col_x) < 0—either MIN or MAX is negative, not both. HAVING MIN (col_x) * MAX (col_x) > 0—col_x is either all positive or all negative. HAVING MIN (SIGN (col_x)) = ...
Get Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
