This presentation describes SQL Aggregate function covering SQL COUNT(), SUM(), MAX(), MIN(), AVG) functions with examples.

Click to access all Slides..

Transcript

What is Aggregate function in SQL?

★ Aggregate functions helps to summarize the large volumes of data.

★ This function can produced a single value for an entire group or table.

★ They operate on sets of rows and return results based on groups of rows.

List of Aggregate Functions

★ COUNT

★ SUM

★ AVERAGE

★ MAX

★ MIN

COUNT() function

The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets on the number of rows or non NULL column values.

SQL Syntax : COUNT(*) , COUNT( [ALL|DISTINCT] expression )

MySQL, PostgreSQL, and SQL Server supports the SQL Syntax

DB2 and Oracle Syntax :

COUNT ({*|[DISTINCT] expression}) OVER (window_clause)

Example : COUNT()

Example : SELECT COUNT(*)

FROM product_mast;

Example : COUNT() with WHERE

Example : SELECT COUNT(*)

FROM product_mast

WHERE rate>=20;

Example : COUNT() with DISTINCT

Example : SELECT

COUNT(DISTINCT company)

FROM product_mast;

Example : COUNT() with GROUP BY

Example : SELECT company, COUNT(*)

FROM product_mast GROUP BY company;

Example : COUNT() with HAVING

Example : SELECT company, COUNT(*) FROM

product_mast GROUP BY company

HAVING COUNT(*)>2;

SUM() function

The SQL AGGREGATE SUM() function returns the sum of all selected column.

SQL Syntax : SUM ([ALL | DISTINCT] expression )

MySQL, PostgreSQL, and SQL Server supports the SQL Syntax

DB2 and Oracle Syntax :

SUM ([ALL | DISTINCT] expression ) OVER (window_clause)

Example : SUM()

Example : SELECT SUM(cost)

FROM product_mast;

Example : SUM() with WHERE

Example : SELECT SUM(cost)

FROM product_mast

WHERE qty>3;

Example : SUM() with GROUP BY

Example : SELECT SUM(cost)

FROM product_mast

WHERE qty>3

GROUP BY

company;

Example : SUM() with HAVING

Example : SELECT company, SUM(cost)

FROM product_mast

GROUP BY company

HAVING SUM(cost)>=170;

AVG() function

The SQL AVG function calculates the average value of a column of numeric type.

It returns the average of all non NULL values.

SQL Syntax : AVG ([ALL | DISTINCT] expression )

MySQL, PostgreSQL, and SQL Server supports the SQL Syntax

DB2 and Oracle Syntax :

AVG ([ALL | DISTINCT] expression ) OVER (window_clause)

Example : AVG()

Example : SELECT AVG(cost)

FROM product_mast;

Example : AVG() with HAVING

Example : SELECT company, AVG(cost)

FROM product_mast

GROUP BY company

HAVING AVG(cost)>=65;

MAX() function

The aggregate function SQL MAX() is used to find the maximum value or highest value of a certain column or expression. This function is useful to determine the largest of all selected values of a column.

SQL Syntax : MAX ([ALL | DISTINCT] expression )

MySQL, PostgreSQL, and SQL Server supports the SQL Syntax

DB2 and Oracle Syntax :

MAX ([ALL | DISTINCT] expression ) OVER (window_clause)

Example : MAX()

Example : SELECT MAX(rate)

FROM product_mast;

Example : MAX() with HAVING

Example : SELECT company, MAX(rate)

FROM product_mast

GROUP BY company

HAVING MAX(rate)=30;

MIN() function

The aggregate function SQL MIN() is used to find the minimum value or lowest value of a column or expression. This function is useful to determine the smallest of all selected values of a column.

Syntax : MIN([ALL | DISTINCT] expression )

MySQL, PostgreSQL, and SQL Server supports the SQL Syntax

DB2 and Oracle Syntax :

MIN ([ALL | DISTINCT] expression ) OVER (window_clause)

Example : MIN()

Example : SELECT MAX(rate)

FROM product_mast;

Example : MIN() with HAVING

Example : SELECT company, MIN(rate)

FROM product_mast

GROUP BY company

HAVING MIN(rate)<20;