- multi input => 1 output
- Not everyone realizes this, but the COUNT function will only include the records in the count where the value of expression in COUNT(expression) is NOT NULL. When expression contains a NULL value, it is not included in the COUNT calculations.
- không thể sử dụng kiểu: select count( * ), job from emp where count( * ) > 2 group by job
- Nếu câu select có chứa group function và 1 cột khác, thì bắt buộc phải có cột này trong group by, nếu không sẽ lỗi
- Aggregate Functions: When you use GROUP BY, any non-aggregate columns in your SELECT list must also appear in the GROUP BY clause. The aggregate functions will then operate on the rows within each defined group.
- select col1, min(col2) from tab1 group by col3 => lỗi, phải sửa thành group by col1, col3
- LISTAGG: The Oracle/PLSQL LISTAGG function concatenates values of the measure_column for each GROUP based on the order_by_clause.
- cú pháp: LISTAGG (measure_column [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]
- Parameters or Arguments
- measure_column: The column or expression whose values you wish to concatenate together in the result set. Null values in the measure_column are ignored.
- delimiter: Optional. It is the delimiter to use when separating the measure_column values when outputting the results.
- order_by_clause: It determines the order that the concatenated values (ie: measure_column) are returned.
- link: https://www.techonthenet.com/oracle/functions/listagg.php
- "You can use column alias in the GROUP BY clause." Not always: Column aliases defined in the SELECT clause generally cannot be used in the GROUP BY clause because GROUP BY is processed before SELECT.
- You cannot use a column alias in the GROUP BY clause. The GROUP BY clause requires the actual column name to group the data properly.
- The HAVING clause cannot use aliases for the column