The usage of Aggregate functions count, max, min, sum, avg, and NVL in oracle,

The usage of Aggregate functions count, max, min, sum, avg, and NVL in oracle,
Usage of Aggregate functions count, max, min, sum, avg, and NVL in oracle

The grouping function aggregate function performs computation on a column in a group of rows and returns a single value. The aggregate function ignores NULL values. Aggregate functions are often used together with the group by clause of SELECT statements, so they are also called grouping functions sometimes. This type of function is usually used in report statistics. The following shows the application of common Oracle Aggregate functions.

Grouping Functions Act on a group of data and return a value for a group of data.

Common grouping functions include:

Count

Used to calculate the number of valid data

Min

 

Returns the minimum value of a numeric column or computed column.

Select gi. ID, Gi. game_instance_name, Gi. draw_no, Gi. draw_date, Count (*) ticketNumber,--- Used to calculate the number of valid data Nvl (min (tt. total_bets), 0) totalEntry,-- Used to calculate the minimum value of a given field. If it is NULL, it is replaced by 0. Nvl (min (tt. total_amount), 0) TotalTurnover---- Used to calculate the minimum value of a given field. If it is NULL, it is replaced by 0. From te_bg_ticket tt, BG_GAME_INSTANCE gi, game g Where tt. BG_GAME_INSTANCE_ID = gi. ID And gi. game_id = g. game_id And gi. status = 1 And tt. ticket_type = 1 And tt. is_count_in_pool = 1 And g. game_id = '4028822f483fd59401483fe62dc4000d' And g. game_type_id = 6 Groupby gi. ID, gi. game_instance_name, gi. draw_no, gi. draw_date Havingcount (*)> 0 Orderby gi. draw_no

 

 

Max

 

Returns the maximum value of a numeric column or computed column.

Same as the minimum value

 

Sum

 

Returns a numeric column or calculates the total number of columns.

Select gi. ID, Gi. game_instance_name, Gi. draw_no, Gi. draw_date, Count (*) ticketNumber,--- Used to calculate the number of valid data Nvl (sum (tt. total_bets), 0) totalEntry,-- Used to calculate the sum of a given field. If it is NULL, it is replaced by 0. Nvl (sum (tt. total_amount), 0) TotalTurnover---- Used to calculate the sum of a given field. If it is NULL, it is replaced by 0. From te_bg_ticket tt, BG_GAME_INSTANCE gi, game g Where tt. BG_GAME_INSTANCE_ID = gi. ID And gi. game_id = g. game_id And gi. status = 1 And tt. ticket_type = 1 And tt. is_count_in_pool = 1 And g. game_id = '4028822f483fd59401483fe62dc4000d' And g. game_type_id = 6 Groupby gi. ID, gi. game_instance_name, gi. draw_no, gi. draw_date Havingcount (*)> 0 Orderby gi. draw_no

 

 

Avg

 

Returns the average value of a numeric column or computed column.

The usage is the same as that of Max, min, and Sum.

 

NVL Function

NVL (expr1, expr2) if the first oracle parameter is null, the value of the second parameter is displayed. If the value of the first parameter is not empty, the original value of the first parameter is displayed.