SQL Aggregate Functions

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

The AVG() Function :

The AVG() function returns the average value of a numeric column.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

 

 SQL COUNT() Function :

The COUNT() function returns the number of rows that matches a specified criteria.

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name;

 

SQL MAX() Function :

The MAX() function returns the largest value of the selected column.

SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name;

 

SQL MIN() Function :

The MIN() function returns the smallest value of the selected column.

SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name;

 

SQL SUM() Function :

The SUM() function returns the total sum of a numeric column.

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name;

 

SQL GROUP BY Statement :

Aggregate functions often need an added GROUP BY statement.

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

 

 SQL UCASE() Function :

The UCASE() function converts the value of a field to uppercase.

SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name;

SQL LCASE() Function :

The LCASE() function converts the value of a field to lowercase.

SQL LCASE() Syntax

SELECT LCASE(column_name) FROM table_name;

SQL JOIN

SQL JOIN

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Different types of SQL JOIN

Before we continue with examples, we will list the types the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

SQL INNER JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

PS! INNER JOIN is the same as JOIN.

SQL LEFT JOIN:

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Examples:

Create two tables Persons and Orders,Note that P_ID is primary key in the “Persons” table,that means that no two columns can have same P_ID.P_ID distinguishes two person even if they have the same name.P_ID in the orders table is foreign key in that table and O_ID is primary key key in the orders table

Persons table

P_ID	Lastname	Firstname	Address	City
1	Hasen	Ola	Timoteivn	Sandnes
2	Svedson	Tove	Borgvn	Sandnes
3	Pettersen	kari	storgt	stavanger

Orders table

O_ID	Orderno	P_ID
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34568	15