Tutorial SQL - SQL aggregation functions
Wed Apr 11, 2018 8:55 am
SQL aggregation functions
The aggregation functions in the SQL language make it possible to perform statistical operations on a set of records. Given that these functions apply to multiple lines at the same time, they allow operations that are used to retrieve the smallest, largest record, or even determine the average value across multiple records.
List of statistical aggregation functions
Aggregate functions are ideal for performing some basic statistics on tables. The main functions are:
AVG () to calculate the average on a record set
COUNT () to count the number of records on a separate table or column
MAX () to retrieve the maximum value of a column on a row set. This applies for both numeric and alphanumeric data
MIN () to retrieve the minimum value in the same way as MAX ()
SUM () to calculate the sum on a record set
Simple use
The most general use is to use the following syntax:
SELECT function (column) FROM table
The COUNT () function has a subtlety. To count the total number of rows in a table, use the star "*" which means that the count has been counting on all columns. The syntax would then be:
All of these functions make sense when used with the GROUP BY command, which filters data on one or more columns. Imagine a table that contains all purchases on a site with the amount of each purchase for each record. To get the total sales by customers, you can run the following query:
The aggregation functions in the SQL language make it possible to perform statistical operations on a set of records. Given that these functions apply to multiple lines at the same time, they allow operations that are used to retrieve the smallest, largest record, or even determine the average value across multiple records.
List of statistical aggregation functions
Aggregate functions are ideal for performing some basic statistics on tables. The main functions are:
AVG () to calculate the average on a record set
COUNT () to count the number of records on a separate table or column
MAX () to retrieve the maximum value of a column on a row set. This applies for both numeric and alphanumeric data
MIN () to retrieve the minimum value in the same way as MAX ()
SUM () to calculate the sum on a record set
Simple use
The most general use is to use the following syntax:
SELECT function (column) FROM table
The COUNT () function has a subtlety. To count the total number of rows in a table, use the star "*" which means that the count has been counting on all columns. The syntax would then be:
- Code:
SELECT COUNT (*) FROM table
Use with GROUP BY
All of these functions make sense when used with the GROUP BY command, which filters data on one or more columns. Imagine a table that contains all purchases on a site with the amount of each purchase for each record. To get the total sales by customers, you can run the following query:
- Code:
SELECT client, SUM (price)
FROM purchase
GROUP BY client
Permissions in this forum:
You cannot reply to topics in this forum