SQL Aggregate functions, slides presentation

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;