0001 ---
0002 layout: global
0003 title: HAVING Clause
0004 displayTitle: HAVING Clause
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `HAVING` clause is used to filter the results produced by
0025 `GROUP BY` based on the specified condition. It is often used
0026 in conjunction with a [GROUP BY](sql-ref-syntax-qry-select-groupby.html)
0027 clause.
0028
0029 ### Syntax
0030
0031 ```sql
0032 HAVING boolean_expression
0033 ```
0034
0035 ### Parameters
0036
0037 * **boolean_expression**
0038
0039 Specifies any expression that evaluates to a result type `boolean`. Two or
0040 more expressions may be combined together using the logical
0041 operators ( `AND`, `OR` ).
0042
0043 **Note**
0044
0045 The expressions specified in the `HAVING` clause can only refer to:
0046 1. Constants
0047 2. Expressions that appear in GROUP BY
0048 3. Aggregate functions
0049
0050 ### Examples
0051
0052 ```sql
0053 CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
0054 INSERT INTO dealer VALUES
0055 (100, 'Fremont', 'Honda Civic', 10),
0056 (100, 'Fremont', 'Honda Accord', 15),
0057 (100, 'Fremont', 'Honda CRV', 7),
0058 (200, 'Dublin', 'Honda Civic', 20),
0059 (200, 'Dublin', 'Honda Accord', 10),
0060 (200, 'Dublin', 'Honda CRV', 3),
0061 (300, 'San Jose', 'Honda Civic', 5),
0062 (300, 'San Jose', 'Honda Accord', 8);
0063
0064 -- `HAVING` clause referring to column in `GROUP BY`.
0065 SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING city = 'Fremont';
0066 +-------+---+
0067 | city|sum|
0068 +-------+---+
0069 |Fremont| 32|
0070 +-------+---+
0071
0072 -- `HAVING` clause referring to aggregate function.
0073 SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum(quantity) > 15;
0074 +-------+---+
0075 | city|sum|
0076 +-------+---+
0077 | Dublin| 33|
0078 |Fremont| 32|
0079 +-------+---+
0080
0081 -- `HAVING` clause referring to aggregate function by its alias.
0082 SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;
0083 +-------+---+
0084 | city|sum|
0085 +-------+---+
0086 | Dublin| 33|
0087 |Fremont| 32|
0088 +-------+---+
0089
0090 -- `HAVING` clause referring to a different aggregate function than what is present in
0091 -- `SELECT` list.
0092 SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING max(quantity) > 15;
0093 +------+---+
0094 | city|sum|
0095 +------+---+
0096 |Dublin| 33|
0097 +------+---+
0098
0099 -- `HAVING` clause referring to constant expression.
0100 SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING 1 > 0 ORDER BY city;
0101 +--------+---+
0102 | city|sum|
0103 +--------+---+
0104 | Dublin| 33|
0105 | Fremont| 32|
0106 |San Jose| 13|
0107 +--------+---+
0108
0109 -- `HAVING` clause without a `GROUP BY` clause.
0110 SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
0111 +---+
0112 |sum|
0113 +---+
0114 | 78|
0115 +---+
0116 ```
0117
0118 ### Related Statements
0119
0120 * [SELECT Main](sql-ref-syntax-qry-select.html)
0121 * [WHERE Clause](sql-ref-syntax-qry-select-where.html)
0122 * [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
0123 * [ORDER BY Clause](sql-ref-syntax-qry-select-orderby.html)
0124 * [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
0125 * [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
0126 * [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
0127 * [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
