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 more information, see Statement/Type-Related Parameters.

Syntax

SELECT ...

GROUP BY { col_name | expr | position } [ ASC | DESC ],... [ WITH ROLLUP ][ ORDER BY NULL ]

                        [ HAVING < search_condition> ]

  • col_name | expr | position : Specify one or more column names, expressions or aliases. 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

xr

 

=== < Result of SELECT Command in Line 1> ===

 

            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

xr

 

=== < Result of SELECT Command in Line 1> ===

 

            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

xr

 

=== < Result of SELECT Command in Line 1> ===

 

            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

xr

 

=== < Result of SELECT Command in Line 1> ===

 

                      a1                     a2

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

                    301                   300

                    201                   475

 

 

2 rows selected.

 

--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

xr

 

=== < Result of SELECT Command in Line 1> ===

 

                      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

 

 

9 rows selected.