Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
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
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.
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Example:
SELECT COUNT(ProductID)
FROM Products;
SELECT COUNT(*)FROM Products;
SELECT COUNT(*)
FROM Products; WHERE RATE>=20;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV Dr.Anirudh Mangore
Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
2. SUM Function
Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
Syntax
SUM()
or
SUM( [ALL|DISTINCT] expression )
Example: SUM()
SELECT SUM(COST)
FROM PRODUCT_MAST;
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
AVG()
or
AVG( [ALL|DISTINCT] expression )
Example:
SELECT AVG(COST)
FROM PRODUCT_MAST;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV Dr.Anirudh Mangore
Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
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
MAX()
or
MAX( [ALL|DISTINCT] expression )
Example:
SELECT MAX(RATE)
FROM PRODUCT_MAST;
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
MIN()
or
MIN( [ALL|DISTINCT] expression )
Example:
SELECT MIN(RATE)
FROM PRODUCT_MAST;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV Dr.Anirudh Mangore
Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
SQL GROUP BY Statement
The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like
"find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(),
SUM(), AVG()) to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Customer CustomerName ContactNam Address City PostalCo Countr
ID e de y
1 Alfreds Futterkiste Maria Obere Str. 57 Berlin 12209 Germa
Anders ny
2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexic
Emparedados y helados Constitución 2222 D.F. o
3 Antonio Moreno Antonio Mataderos 2312 México 05023 Mexic
Taquería Moreno D.F. o
4 Around the Horn Thomas 120 Hanover Sq. London WA1 UK
Hardy 1DP
5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22 Swede
Berglund n
The following SQL statement lists the number of customers in each country:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV Dr.Anirudh Mangore
Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
The following SQL statement lists the number of customers in each country, sorted high to
low:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
The following SQL statement lists the number of customers in each country. Only include
countries with more than 5 customers:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country, sorted high to
low (Only include countries with more than 5 customers):
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV Dr.Anirudh Mangore
