SQL Aggregate Functions | Domesticated Brain

In this lesson we are working with the following tblproduct table

It has following fields (ProductID, Category, BrandName, ProductName, Price)

SQL Aggregate functions give a single value after processing data in a column. Please Note that these aggregate functions ignore null values

These are the basic aggregate functions and basic syntax in SQL

SQL AVG() Function

SQL AVG() Function returns the average value in a column

SELECT AVG(column_name) FROM table_name;

The following SQL query will give us the average value of the prices in the Price column of the tblproduct table

SELECT AVG(Price) FROM tblproduct

The following SQL query will select the products that exceeding the average price

SELECT BrandName,ProductName,Price FROM tblproduct
WHERE Price>(SELECT AVG(Price) FROM tblproduct);

SQL COUNT() Function returns the number of rows in a column

SELECT COUNT(column_name) FROM table_name;

The following SQL query will give us the number of rows in the tblproduct table

SELECT COUNT(ProductName) FROM tblproduct;

SQL MAX() Function returns the largest value in a column with numerical data

SELECT MAX(column_name) FROM table_name;

The maximum price 85000 will be returned after executing the following query

SELECT MAX(Price) FROM tblproduct;

SQL MIN () Function returns the smallest value in a column with numerical data and is the opposite of max function

SELECT MIN(column_name) FROM table_name;

The minimum price 10000 will be returned after executing the following query

SELECT MIN(Price) FROM tblproduct;

SQL SUM() Function returns the sum of all values in a column with numerical data

SELECT SUM(column_name) FROM table_name;

The sum of the prices will be returned after executing following query

SELECT SUM(Price) FROM tblproduct;