English

简体中文

English

简体中文
On this page

GROUP BY ​

The GROUP BY clause in SQL is used to group rows that have the same values in one or more columns. This clause is typically used with aggregate functions such as COUNT, SUM, AVG, etc., to generate summary reports.

Syntax ​

The basic syntax of the GROUP BY clause is as follows:

sql
SELECT column1, column2, ..., aggregate_function(column_name)
FROM table_name
GROUP BY column1, column2, ...;

The GROUP BY clause groups the result set based on the columns specified in the clause. The aggregate function is applied to each group of rows that have the same values in the specified columns.

Examples ​

Consider the following table named "system_metrics":

sql
+-------+-------+----------+-------------+-----------+---------------------+
| host  | idc   | cpu_util | memory_util | disk_util | ts                  |
+-------+-------+----------+-------------+-----------+---------------------+
| host1 | idc_a |     11.8 |        10.3 |      10.3 | 2022-11-03 03:39:57 |
| host1 | idc_b |       50 |        66.7 |      40.6 | 2022-11-03 03:39:57 |
| host1 | idc_c |     50.1 |        66.8 |      40.8 | 2022-11-03 03:39:57 |
| host1 | idc_e |     NULL |        66.7 |      40.6 | 2022-11-03 03:39:57 |
| host2 | idc_a |     80.1 |        70.3 |        90 | 2022-11-03 03:39:57 |
+-------+-------+----------+-------------+-----------+---------------------+

Group by Tags ​

To get the avg memory_util for each idc, the following SQL query can be used:

sql
SELECT idc, AVG(memory_util)
FROM system_metrics
GROUP BY idc;

The result of the above query would be:

sql
+-------+---------------------------------+
| idc   | AVG(system_metrics.memory_util) |
+-------+---------------------------------+
| idc_b |                            66.7 |
| idc_c |                            66.8 |
| idc_e |                            66.7 |
| idc_a |                            40.3 |
+-------+---------------------------------+

Group by Time Interval ​

To get the avg memory_util for each day, the following SQL query can be used:

sql
SELECT date_trunc('day', ts) as dt, avg(memory_util)
FROM system_metrics
GROUP BY dt

Please refer to date_trunc for more details.