Javatpoint Logo
Javatpoint Logo

SQL Aggregate Functions

  • SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
  • It is also used to summarize the data.

Types of SQL Aggregation Function


DBMS SQL Aggregate Functions

1. COUNT FUNCTION

  • COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
  • COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.

Syntax

Sample table:

PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120

Example: COUNT()

Output:

10

Example: COUNT with WHERE

Output:

7

Example: COUNT() with DISTINCT

Output:

3

Example: COUNT() with GROUP BY

Output:

Com1    5
Com2    3
Com3    2

Example: COUNT() with HAVING

Output:

Com1    5
Com2    3

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

Example: SUM()

Output:

670

Example: SUM() with WHERE

Output:

320

Example: SUM() with GROUP BY

Output:

Com1    150
Com2    170

Example: SUM() with HAVING

Output:

Com1    335
Com3    170

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

Syntax

Example:

Output:

67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Syntax

Example:

30 

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Syntax

Example:

Output:

10

Next TopicDBMS SQL Join




Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA