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 ][ HAVING <search_condition> ]

• col_name | expr | position: Specifies one or more column names, expressions, aliases or column location. Items are separated by commas. Columns are sorted on this basis.
• [ ASC| DESC ]: Specifies 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: Specifies 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. When a WITH ROLLUP modifier is defined in the GROUP BY clause, the result value for all rows of the group is additionally displayed. In other words, total aggregation is made for the value aggregated by group. When there are two columns for Group By, the former is considered as a large unit and the latter is considered as a small unit, so the total aggregation row for the small unit and the total aggregation row for the large unit are added. For example, you can check the aggregation of the sales result per department and salesperson through one query.

Example

--creating a new table

CREATE TABLE sales_tbl

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

INSERT INTO sales_tbl VALUES

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

(201, 'George' , 2, 250),(201, 'Laura'  , 1, 100),

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

(301, 'Max'    , 1, 300),

(301, 'Max'    , 2, 300),

(501, 'Stephan', 1, 300),

(501, 'Stephan', 2, DEFAULT),

(501, 'Chang'  , 1, 150),

(501, 'Chang'  , 2, 150),

(501, 'Sue'    , 1, 150),

(501, 'Sue'    , 2, 200);

 

--selecting rows grouped by dept_no

SELECT dept_no, avg(sales_amount) FROM sales_tbl

GROUP BY dept_no;

      dept_no         avg(sales_amount)

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

          201     3.250000000000000e+02

          301     3.000000000000000e+02

          501     1.750000000000000e+02

--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     4.000000000000000e+02

          301     3.000000000000000e+02

          501     1.900000000000000e+02

 

--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     4.000000000000000e+02

          301     3.000000000000000e+02

 

--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     3.000000000000000e+02

          501     3.000000000000000e+02

          201     4.000000000000000e+02

 

--selecting rows grouped by dept_no, name 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'                 3.500000000000000e+02

          201  'Laura'                  5.000000000000000e+02

          201  NULL                     4.000000000000000e+02

          301  'Max'                    3.000000000000000e+02

          301  NULL                     3.000000000000000e+02

          501  'Chang'                  1.500000000000000e+02

          501  'Stephan'                3.000000000000000e+02

          501  'Sue'                    1.750000000000000e+02

          501  NULL                     1.900000000000000e+02

         NULL  NULL                     2.750000000000000e+02