Scribd
Upload
5 views3 pages

SQL Aggregate Functions Overview

The document provides a list of SQL aggregate functions along with their syntax and purposes. Functions include AVG, MIN, MAX, COUNT, SUM, and various string manipulation functions like LOWER, UPPER, and SUBSTR. Each function is accompanied by an example command for clarity.

Uploaded by

kavitadavim
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

SQL Aggregate Functions Overview

The document provides a list of SQL aggregate functions along with their syntax and purposes. Functions include AVG, MIN, MAX, COUNT, SUM, and various string manipulation functions like LOWER, UPPER, and SUBSTR. Each function is accompanied by an example command for clarity.

Uploaded by

kavitadavim
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL (Aggregate functions)

Function name Syntax and Purpose

1. AVG Purpose: Returns average value of a column

Syntax: Select AVG(column name) from table


name.

Command: Select AVG(salary) from emp;

2. MIN Purpose: Returns minimum value of a column

Syntax: Select MIN(column name) from table


name.

Command: Select MIN(salary) from emp;

3. MAX Purpose: Returns maximum value of a column

Syntax: Select MAX (column name) from table


name.

Command: Select MAX (salary) from emp;

4. Count Purpose: Returns no. of rows where condition Is


not null.

Syntax: Select count (column name) from table


name.

Command: Select count (email id) from emp;


5. Count* Purpose: Returns no. of rows where including
duplicate and null.

Syntax: Select count (column name) from table


name.

Command: Select count (email id) from emp;

6. Sum Purpose: Returns addition of values in a column

Syntax: Select sum (column name) from table


name.

Command: Select sum(marks) from student;

7. Power Purpose: Returns m raised to power n of


number

Syntax: Select power (9,3) from dual.

8. SQRT Purpose: Returns square root of number

Syntax: Select SQRT (9) from dual.

9. LOWER Purpose: Returns char with all small case letter.


Syntax: select LOWER(“MIHIKA”) from dual;

10. UPPER Purpose: Returns char with all upper case letter.
Syntax: select UPPER(“mihika”) from dual;

11. INITCAP Purpose: Returns string with initial capital letter.


Synatx: select INITCAP (mihika) from dual;
12. Length Purpose: Returns length of the string.

Syntax: select length(mihika) from dual;

13. Substr Purpose: Returns string from a main string


Syntax: select Substr(“Mihikaverma”,3,7) from
dual;

[Link] Purpose: Removes character from the left of the string


Syntax: select LTRIM(“Mihika”,”I”) from dual;

[Link] Purpose: Removes character from the right of the


string
Syntax: select RTRIM (“MIHIKA”,”I”) from dual;

16. LPAD Purpose: fill the string with given character from left
position
Select LPAD(‘Page 1’, 10, ‘*’)from dual;

[Link] Purpose: fill the string with given character from right
position
Select RPAD(‘Page 1’, 10, ‘*’)from dual;

You might also like