Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



GROUP BY ... HAVING Clause

Description

The GROUP BY clause is used to group the result retrieved by the SELECT statement based on a specific column. This clause is used to sort by group or to get the aggregation by group using the aggregation function. Herein, a group consists of records that have the same value for the column specified in the GROUP BY clause.

You can also set a condition for group selection by including the HAVING clause after the GROUP BY clause. That is, only groups satisfying the condition specified by the HAVING clause are queried out of all groups that are grouped by the GROUP BY clause.

By SQL standard, you cannot specify a column (hidden column) not defined in the GROUP BY clause to the SELECT column list. However, by using extended CUBRID grammars, you can specify the hidden column to the SELECT column list. If you do not use the extended CUBRID grammars, the only_full_group_by parameter should be set to yes. For details, see Statement/Type-Related Parameters.

Syntax

SELECT ...

GROUP BY { col_name | expr | positoin } [ ASC | DESC ],...

          [ WITH ROLLUP ][ ORDER BY NULL ][ HAVING <search_condition> ]

  • col_name | expr | position : Specify one or more column names, expressions, aliases or column location. Items are separated by commas. Columns are sorted on this basis.
  • [ ASC| DESC ] : Specify the ASC or DESC sorting option after the columns specified in the GROUP BY clause. If the sorting option is not specified, the default value is ASC.
  • search_condition : Specify the search condition in the HAVING clause. In the HAVING clause you can refer to the hidden columns not specified in the GROUP BY clause as well as to columns and aliases specified in the GROUP BY clause and columns used in aggregate functions.
  • WITH ROLLUP : If you specify the WITH ROLLUP modifier in the GROUP BY clause, the aggregate information of the result value of each GROUPed BY column is displayed for each group, and the total of all result rows is displayed at the last row.
  • ORDER BY NULL : You can avoid the sorting overhead caused by GROUP BY by specifying the ORDER BY NULL modifier in the GROUP BY clause.
Example

--creating a new table

CREATE TABLE sales_tbl

(dept_no int, name VARCHAR(20) PRIMARY KEY, sales_month int, sales_amount int DEFAULT 100);

INSERT INTO sales_tbl VALUES

(201, 'George' , 1, 450),

(201, 'Laura'  , 2, 500),

(301, 'Max'    , 4, 300),

(501, 'Stephan', 4, DEFAULT),

(501, 'Chang'  , 5, 150),

(501, 'Sue'    , 6, 150),

(NULL, 'Yoka'   ,4, NULL);

 

--selecting rows grouped by dept_no with ORDER BY NULL modifier

SELECT dept_no, avg(sales_amount) FROM sales_tbl

GROUP BY dept_no ORDER BY NULL;

      dept_no  avg(sales_amount)

================================

         NULL               NULL

          201                475

          301                300

          501                133

 

--conditions in WHERE clause operate first before GROUP BY

SELECT dept_no, avg(sales_amount) FROM sales_tbl

WHERE sales_amount > 100 GROUP BY dept_no;

      dept_no  avg(sales_amount)

================================

          201                475

          301                300

          501                150

 

--conditions in HAVING clause operate last after GROUP BY

SELECT dept_no, avg(sales_amount) FROM sales_tbl

WHERE sales_amount > 100 GROUP BY dept_no HAVING avg(sales_amount) > 200;

      dept_no  avg(sales_amount)

================================

          201                475

          301                300

 

--selecting and sorting rows with using column alias

SELECT dept_no AS a1, avg(sales_amount) AS a2 FROM sales_tbl

WHERE sales_amount > 200 GROUP BY a1 HAVING a2 > 200 ORDER BY a2;

           a1           a2

==========================

          301          300

          201          475

 

--selecting rows grouped by dept_no with WITH ROLLUP modifier

SELECT dept_no AS a1, name AS a2, avg(sales_amount) AS a3 FROM sales_tbl

WHERE sales_amount > 100 GROUP BY a1,a2 WITH ROLLUP;

           a1  a2                             a3

================================================

          201  'George'                      450

          201  'Laura'                       500

          201  NULL                          475

          301  'Max'                         300

          301  NULL                          300

          501  'Chang'                       150

          501  'Sue'                         150

          501  NULL                          150

         NULL  NULL                          310