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.
SELECT ...
GROUP BY { col_name | expr | positoin } [ ASC | DESC ],...
[ WITH ROLLUP ][ ORDER BY NULL ][ HAVING <search_condition> ]
--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