OpenSearch:GROUP BY

Description

The GROUP BY clause aggregates data from one or more fields to calculate values such as maximums, minimums, and averages.

Syntax

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

Built-in UDAFs

The following table describes the built-in UDAFs.

No.	Function name	Features
1	SUM	Calculates the sum after aggregation.
2	MIN	Finds the minimum value after aggregation.
3	MAX	Finds the maximum value after aggregation.
4	COUNT	Counts the number of rows.
5	AVG	Calculates the average value after aggregation.
6	ARBITRARY	Selects an arbitrary input value as the output.

You can customize UDAFs to extend the aggregation logic.

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