OpenSearch:GROUP BY

Overview

GROUP BY is used to aggregate one or more fields to calculate the metrics for the required data, such as the maximum value, minimum value, and average value.

Supported versions

OpenSearch Retrieval Engine Edition whose HA3 version is V3.7.0 or later

​

Syntax

select:
    SELECT [ ALL | DISTINCT ]
    { * | projectItem [, projectItem ]* }
    FROM tableExpression
    [ WHERE booleanExpression ]
    [ GROUP BY { groupItem [, groupItem ]* } ]
    [ HAVING booleanExpression ]

Built-in UDAFs

The following table describes built-in user-defined aggregate functions (UDAFs).

You can customize a UDAF to extend the logic that is used to aggregate data. For more information, see UDAF overview.

Examples

1. SUM

SELECT brand, SUM(price) FROM phone WHERE nid < 8 GROUP BY brand

2. MIN

SELECT brand, MIN(price) FROM phone WHERE nid < 8 GROUP BY brand

3. MAX

SELECT brand, MAX(price) FROM phone WHERE nid < 8 GROUP BY brand

4. COUNT

SELECT brand, COUNT(*) FROM phone WHERE nid < 8 GROUP BY brand

5. AVG

SELECT brand, AVG(price) FROM phone WHERE nid < 8 GROUP BY brand

6. HAVING clause

SELECT brand FROM phone GROUP BY brand HAVING COUNT(brand) > 10