All Products
Search

This Product

    Simple Log Service:GROUP BY clause

    Document Center

    Simple Log Service:GROUP BY clause

    Last Updated:Jun 20, 2026

    The GROUP BY clause works with aggregate functions to group rows from a result set based on matching values in one or more columns. For each group, the query returns a single summary row, simplifying data analysis. You can also use the GROUP BY clause with the ROLLUP, CUBE, and GROUPING SETS clauses to extend its grouping capabilities and provide more versatile analysis options.

    Syntax

    GROUP BY

    Groups the rows from a result set.

    SELECT 
 key1,
  ...
 aggregate_function
 GROUP BY 
 key,...

    Parameters

    • key1: The column to group rows by. You can group by log field names or by the result columns of an aggregate function. The GROUP BY clause supports grouping by single or multiple columns.

    • aggregate_function: The aggregate function applied to each group, such as count, min, max, avg, or sum.

    GROUP BY ROLLUP

    The GROUP BY ROLLUP clause generates a result set with rows for each group, plus additional subtotal and grand total rows. For example, GROUP BY ROLLUP (a, b) produces a result set for the grouping sets (a, b), (a, null), and (null, null).

    SELECT
key1,
 ...
aggregate_function 
GROUP BY ROLLUP (key1,...)

    Parameters

    • key1: The column to group rows by. You can group by log field names or by the result columns of an aggregate function. The GROUP BY clause supports grouping by single or multiple columns.

    • aggregate_function: The aggregate function applied to each group, such as count, min, max, avg, or sum.

    GROUP BY CUBE

    The GROUP BY CUBE clause generates a result set that includes groups for all possible combinations of the specified columns. For example, GROUP BY CUBE (a, b) produces a result set for the grouping sets (a, b), (null, b), (a, null), and (null, null).

    SELECT 
key1,
...
aggregate_function 
GROUP BY CUBE (key1,...)

    Parameters

    • key1: The column to group rows by. You can group by log field names or by the result columns of an aggregate function. The GROUP BY clause supports grouping by single or multiple columns.

    • aggregate_function: The aggregate function applied to each group, such as count, min, max, avg, or sum.

    GROUP BY GROUPING SETS

    The GROUP BY GROUPING SETS clause lets you specify multiple distinct groupings in a single query. For example, GROUP BY GROUPING SETS (a, b) is equivalent to combining the results of GROUP BY a and GROUP BY b. The result set includes the grouping sets (a, null) and (null, b).

    SELECT 
key1,
... 
aggregate_function
GROUP BY GROUPING SETS (key1,...)

    Parameters

    • key1: The column to group rows by. You can group by log field names or by the result columns of an aggregate function. The GROUP BY clause supports grouping by single or multiple columns.

    • aggregate_function: The aggregate function applied to each group, such as count, min, max, avg, or sum.

    Examples

    Important

    When using a GROUP BY clause in a query statement, the SELECT list can only contain:

    • The columns specified in the GROUP BY clause.

    • An aggregate function that performs a calculation on a column, such as COUNT() or SUM().

    You cannot select a column that is not in the GROUP BY clause or an aggregate function. This is because each group can have multiple values for that column, making its value in the summary row ambiguous.

    * | SELECT status, request_time, COUNT(*) AS PV GROUP BY status

    The query is invalid because the request_time column is not included in the GROUP BY clause and is not processed by an aggregate function.

    To correct the query, use an aggregate function:

    * | SELECT status, arbitrary(request_time), COUNT(*) AS PV GROUP BY status

    In this query, arbitrary(request_time) is an aggregate function that returns an arbitrary value from the request_time column within each group. This approach satisfies SQL syntax rules and allows you to include the column in your query.

    • Example 1

      Calculate the page views (PV) for each status code.

      • Query statement

        * | SELECT status, count(*) AS PV GROUP BY status

      • Query results: A table with status and PV columns, showing the page view count for each HTTP status code.

    • Example 2

      Calculate the hourly page views (PV) for your website.

      • Query statement

        The __time__ field is a reserved field in Simple Log Service that represents the time column. In this query, time is an alias for date_trunc('hour', __time__). For more information about the date_trunc function, see date_trunc function.

        * |
SELECT
  count(*) AS PV,
  date_trunc('hour', __time__) AS time
GROUP BY
  time
ORDER BY
  time
LIMIT
  1000

      • Query results: The query returns a result set with two columns, PV and time. Sample data includes PVs of 1202, 10159, and 28001, corresponding to the times 2021-08-10 00:00:00.000, 2021-08-10 01:00:00.000, and 2021-08-10 02:00:00.000, respectively.

    • Example 3

      Calculate PV at a 5-minute time granularity.

      • Query statement

        The date_trunc function can only group data by a fixed interval. To group data by a custom time interval, use the modulo operation. For example, % 300 aligns timestamps to a 5-minute (300 seconds) time granularity.

        * |
SELECT
  count(*) AS PV,
  __time__-__time__ % 300 AS time
GROUP BY
  time
LIMIT
  1000

      • Query results: The query returns three sample records: PV=143 for time=1628525100, PV=31 for time=1628526600, and PV=44 for time=1628526900.

    • Example 4

      Group data by request method and status independently. This query calculates the total page views for each request method and for each status code.

      • Query statement

        * |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
  GROUPING SETS (request_method, status)

      • Query results: When the results are grouped by request_method, the status column is null. The PVs are: GET (189), POST (47), PUT (39), DELETE (9), and HEAD (1). When the results are grouped by status, the request_method column is null. The PVs are: 200 (259), 201 (2), and 202 (2).

    • Example 5

      Group data by request method and status. The query generates a result set for all possible combinations of these columns, including (null, null), (request_method, null), (null, status), and (request_method, status). It then calculates the page views for each of these groups.

      • Query statement

        * |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
 CUBE (request_method, status)

      • Query results: The query returns 43 records with three columns: request_method, status, and PV. In the summary rows generated by CUBE, a null value indicates a subtotal for that dimension. For example, a row with null/null has a PV of 285 (the grand total). A row with GET/null has a PV of 189, POST/null has 47, PUT/null has 39, DELETE/null has 9, and HEAD/null has 1. Rows for status subtotals include null/200 with a PV of 259, and null/201 and null/202 each with a PV of 2.

    • Example 6

      Group data by request method and status hierarchically. The query generates a result set for the grouping sets (request_method, status), (request_method, null), and (null, null), and then calculates the page views for each of these groups.

      • Query statement

        * |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
 ROLLUP (request_method, status)

      • Query results: The query returns 33 records with three columns: request_method, status, and PV. The ROLLUP function generates summary rows at each grouping level. A null value indicates that the row represents a subtotal. For example, the row GET / null / 189 shows that the total PV for the GET method across all status codes is 189. The row null / null / 285 shows that the grand total PV for all methods and status codes is 285.