To execute an aggregate function and pass all values from a column to it, use the following syntax:
The function will take all values from the specified column and produce a single cell. Consequently, running n aggregate functions simultaneously produces n cells.
Vendors of different SQL-compatible database management systems provide their users with slightly different aggregate functions. Here is a list of aggregate functions according to the ANSI SQL standard:
There are also a few more elaborate functions related to statistics, such as STDDEV_POP for the population standard deviation or CORR for the correlation coefficient. It may be worth your while to check out the complete list of aggregate functions.
Consider this table named stocks:
Using the MAX function to find the highest price among all the stocks:
This query will produce 89.8. Likewise, the MIN function for the same column would return 15.6. To find out the count of deals made yesterday, use this query:
It will return 65.
Numeric types like INT or REAL naturally fit most aggregate functions. However, it also makes sense to use aggregate functions with other data types in some cases. For example, the MIN and MAX functions can be used to find the lexicographically smallest and largest strings.
Share this article
Get more lessons like this
Thank you! Your submission has been received!
Oops! Something went wrong.
Level up your tech skills and advance your career
• Wide range of SQL and Databases tracks
• Study at your own pace with your personal study plan
It is also possible to use WHERE to choose a subset of rows on which we want to run our aggregation functions. For example, to find the average price and average count of deals for all stocks that cost more than 40, the query will be:
AVG(price) AS avg_price,
AVG(yesterday_deals) AS avg_deals
price > 40;
The answer will be:
To omit all duplicate values, place the DISTINCT keyword inside the brackets of your aggregate function:
SELECT COUNT(DISTINCT yesterday_deals)
This query will return 4 as there are only four distinct numeric values in the column yesterday_deals.
Determining the set of unique values can be costly in terms of computation complexity, so don't overuse DISTINCT.
A regular call of the COUNT function with a column name as an argument will count the total amount of values in the column. If you call COUNT with an asterisk, you're telling the function to count all rows in the table. The final result won't be affected by the particular types of columns or the values that their cells store. For our stocks table, COUNT(*) will return 7.
All aggregate functions except COUNT(*) ignore the NULL values.
As data drives decisions in modern industries, understanding how to store, retrieve, and manage it is crucial. Our tracks cover the fundamentals of SQL, teaching you to interact with databases efficiently.