Count,sum,avg,min,max functions in Sql

Tips>>SQL

MAKE FULL WEB APPLICATION WITH JUST SQL KNOWLEDGE? CLICK HERE

Five Important aggregate functions are COUNT, SUM, AVG, MIN, and MAX. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.

1. COUNT (*) gives the number of rows satisfying the conditions

2. SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.

3. AVG () gives the average of the given column.

4. MIN () gives the smallest figure in the given column.

5. MAX () gives the largest figure in the given column.

1. COUNT (*)

The keyword COUNT can be used together to count the number of distinct results.

Syntax :-

SELECT COUNT (column) FROM table

EXAMPLE:-

With this "Persons" Table:

NAME	AGE
Varsha	34
Ranjan	45
Devi

This example finds the number of persons with a value in the "Age" filled in the "Persons" table:

SELECT COUNT (Age) FROM Persons

RESULT:-

The COUNT (column) function is handy for finding columns without a value. Note that the result is one less than the number of rows in the original table because one of the persons does not have an age value stored.

2. SUM ()

The SUM function returns the total sum of a column in a given selection. NULL values are not included in the calculation.

Syntax :-

SELECT SUM (column) FROM table

EXAMPLE I :-

With this "Persons" Table:

NAME	AGE
Varsha	34
Ranjan	45
Devi	19

This example returns the sum of all ages in the "person" table:

SELECT SUM (Age) FROM Persons

RESULTS:-

EXAMPLE II :-

This example returns the sum of ages for persons that are more than 20 years old:

SELECT SUM(Age) FROM Persons WHERE Age>20

RESULT:-

3. AVG ()

The AVG function returns the average value of a column in a selection. NULL values are not included in the calculation.

Syntax :-

SELECT AVG (column) FROM table

EXAMPLE I :-

This example returns the average age of the persons in the "Persons" table:

SELECT AVG(Age) FROM Persons

RESULT :-

32.67

EXAMPLE II :-

This example returns the average age for persons that are older than 20 years:

SELECT AVG(Age) FROM Persons WHERE Age>20

RESULT:-

39.5

4. MIN ()

The MIN function returns the lowest value in a column. NULL values are not included in the calculation.

Syntax :-

SELECT MIN(column) FROM table

EXAMPLE:-

SELECT MIN(Age) FROM Persons

RESULT:-

5. MAX ()

The MAX function returns the highest value in a column. NULL values are not included in the calculation.

Syntax :-

SELECT MAX(column) FROM table

EXAMPLE:-

SELECT MAX(Age) FROM Persons

RESULT:-

Note: The MIN and MAX functions can also be used on text columns, to find the highest or lowest value in alphabetical order.

If you don't find what you are looking for. Please click here to submit your query, our experts will reply soon.

E-mail : sales@virtualsplat.com
Phone : +91-9892413501

Whatsapp Icon +91-9967648641