Scribd
Upload
251 views2 pages

SQL Aggregate Functions Explained

The document discusses various SQL aggregate functions and clauses including COUNT, SUM, MAX, MIN, AVG, GROUP BY, HAVING, and ROUND. It provides examples of how to use each function or clause to return aggregated results from columns in a table such as counting rows, getting totals, maximums, minimums, and averages. It also covers how the GROUP BY clause groups results and the HAVING clause filters groups based on aggregates.

Uploaded by

Zeyad Alodat
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
251 views2 pages

SQL Aggregate Functions Explained

The document discusses various SQL aggregate functions and clauses including COUNT, SUM, MAX, MIN, AVG, GROUP BY, HAVING, and ROUND. It provides examples of how to use each function or clause to return aggregated results from columns in a table such as counting rows, getting totals, maximums, minimums, and averages. It also covers how the GROUP BY clause groups results and the HAVING clause filters groups based on aggregates.

Uploaded by

Zeyad Alodat
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
  • Aggregate Functions Overview

Cheatsheets / Learn SQL

Aggregate Functions
Column References
The GROUP BY and ORDER BY clauses can
reference the selected columns by number in which they SELECT COUNT(*) AS 'total_movies',
appear in the SELECT statement. The example query rating
will count the number of movies per rating, and will: FROM movies
GROUP BY 2
●
GROUP BY column 2 ( rating ) ORDER BY 1;
●
ORDER BY column 1 ( total_movies )

MAX() Aggregate Function


The MAX() aggregate function takes the name of a
column as an argument and returns the largest value in a SELECT MAX(amount)
column. The given query will return the largest value from FROM transactions;
the amount column.

SUM() Aggregate Function


The SUM() aggregate function takes the name of a
column as an argument and returns the sum of all the SELECT SUM(salary)
value in that column. FROM salary_disbursement;

COUNT() Aggregate Function


The COUNT() aggregate function returns the total
number of rows that match the speci ed criteria. For SELECT COUNT(*)
instance, to nd the total number of employees who have FROM employees
less than 5 years of experience, the given query can be WHERE experience < 5;
used.
Note: A column name of the table can also be used
instead of * . Unlike COUNT(*) , this variation
COUNT(column) will not count NULL values in that
column.

GROUP BY Clause
The GROUP BY clause will group records in a result set
by identical values in one or more columns. It is often SELECT rating,
used in combination with aggregate functions to query COUNT(*)
information of similar records. The GROUP BY clause FROM movies
can come after FROM or WHERE but must come GROUP BY rating;
before any ORDER BY or LIMIT clause.
The given query will count the number of movies per
rating.

/
MIN() Aggregate Function
The MIN() aggregate function returns the smallest
value in a column. For instance, to nd the smallest value SELECT MIN(amount)
of the amount column from the table named FROM transactions;
transactions , the given query can be used.

AVG() Aggregate Function


The AVG() aggregate function returns the average
value in a column. For instance, to nd the average SELECT AVG(salary)
salary for the employees who have less than 5 years FROM employees
of experience, the given query can be used. WHERE experience < 5;

HAVING Clause
The HAVING clause is used to further lter the result
set groups provided by the GROUP BY clause.
SELECT year,
COUNT(*)
HAVING is often used with aggregate functions to lter
the result set groups based on an aggregate property. The
FROM movies
given query will select only the records (rows) from only GROUP BY year
years where more than 5 movies were released per year. HAVING COUNT(*) > 5;

ROUND() Function
The ROUND() function will round a number value to a
speci ed number of places. It takes two arguments: a SELECT year,
number, and a number of decimal places. It can be ROUND(AVG(rating), 2)
combined with other aggregate functions, as shown in the FROM movies
given query. This query will calculate the average rating of WHERE year = 2015;
movies from 2015, rounding to 2 decimal places.

Aggregate Functions
Aggregate functions perform a calculation on a set of
values and return a single value:

●
COUNT()
●
SUM()
●
MAX()
●
MIN()
●
AVG()

Common questions

Powered by AI

The SUM() function calculates the total sum of a given column across the selected records, while the AVG() function computes the average value of a specified column. Both functions take a column as an argument and return a single computed value based on all rows that match the criteria. SUM() is used for total aggregation, suitable for tasks like finding total salary disbursement, whereas AVG() is for determining mean values, such as finding the average salary of employees with less than five years of experience .

The MAX() and MIN() aggregate functions provide the highest and lowest values in a dataset, respectively, which allow users to determine the range and assess variability within the data. By applying these functions on relevant columns—such as amounts in a transactions table—users can quickly identify the scope of data values and gain insights into data distribution or identify outliers .

Ordering by column number in the ORDER BY clause can make queries less readable and harder to maintain because it requires understanding the exact position of each selected column. This can lead to errors if the column order changes or if the SELECT statement becomes complex. In contrast, ordering by column name improves clarity by explicitly specifying the sorting criteria, making the query more intuitive and easier to maintain .

The GROUP BY clause groups records in a result set by identical values in specified columns, allowing for aggregated computations such as counting, summing, or averaging to be applied to each distinct group. The HAVING clause then allows further filtering of these groups based on conditions applied to the aggregated data. This enables more detailed and organized data analysis than mere raw data retrieval, facilitating insights such as analyzing the number of movies released per year where each year had more than five releases .

Using COUNT(column) excludes NULL values from the count, focusing only on non-null entries in the specified column, which can provide a more accurate reflection of existing data. COUNT(*) counts all rows, including those with NULLs, thus offering a higher count when NULLs are present. This distinction is crucial in contexts where NULL values signify missing data or where the presence of complete data is required for analyses .

Using column references by number in GROUP BY and ORDER BY can make queries shorter but can also lead to errors if the column order in the SELECT clause changes, as reference numbers may no longer align with the desired columns. While this practice might slightly optimize query execution by eliminating the need for string comparison, it introduces risks in readability and maintainability, making it less advisable in complex or frequently-altered queries .

The AVG() function can evaluate average performance metrics or salary distributions, providing benchmarks against which individual performance or salaries are assessed. In practical implementation, an SQL query using AVG() might assess the average salary for employees with less than five years of experience—highlighting salary trends among newer employees and enabling comparative analysis with company-wide averages .

When COUNT() is applied to a specific column, it does not count rows where the column value is NULL, which can lead to a lower count than when using COUNT(*), where all rows are counted irrespective of their content. This behavior is crucial in data interpretation as it impacts the perceived completeness and reliability of data analyses, particularly in datasets where NULLs represent missing or intentionally omitted data points .

The HAVING clause allows filtering of grouped results based on aggregate values, enabling deeper business insights such as identifying key performance indicators. For example, it can filter to show only those years where the number of movies released exceeds a threshold, thereby highlighting high-production periods or trends without requiring a separate analysis phase. This capability is essential for strategic decision-making in business contexts that rely on data summaries, comparisons, and conditions beyond simple queries .

The ROUND() function enhances the readability and precision control of outputs from aggregate functions such as AVG() that involve floating-point numbers by truncating the number to a specified number of decimal places. This is particularly useful in financial and statistical analyses where consistent number formatting is required, such as calculating and presenting an average rating rounded to two decimal places .

/ The GROUP BY and ORDER BY clauses can reference the selected columns by number in which they appear in the SELECT statem
/ The MIN() aggregate function returns the smallest value in a column. For instance, to İnd the smallest value of the amount

You might also like