Aggregate Functions (learnsqlonline.org)

Aggregate functions in SQL enable us to perform calculations on sets of rows and return a single value as the result. These functions can summarize data, calculate statistics, and answer specific questions about our data. Common aggregate functions include SUM, AVG, COUNT, MAX, and MIN, among others.

--Aggregate functions
CREATE TABLE grades (name TEXT, grade INTEGER);

INSERT INTO grades (name, grade) VALUES
    ("John", 97), ("Eric", 88), ("Jessica", 98), ("Mike", 82), ("Jeff", NULL);

SELECT "total students", COUNT(name) FROM grades;
SELECT "total grades", COUNT(grade) FROM grades;
SELECT "sum of grades", SUM(grade) FROM grades;
SELECT "grade average", AVG(grade) FROM grades;
SELECT "lowest grade", MIN(grade) FROM grades;
SELECT "highest grade", MAX(grade) FROM grades;
SELECT "names", GROUP_CONCAT(name) FROM grades;

Aggregate functions simplify data analysis by providing a concise way to obtain summary information from large datasets. Instead of manually iterating through each row, aggregate functions allow us to calculate totals, averages, counts, and other useful metrics in a single query. This streamlines the data analysis process and provides valuable insights.

--Group By Example
CREATE TABLE grades (name TEXT, class INTEGER, grade INTEGER);

INSERT INTO grades (name, class, grade) VALUES
    ("John", 1, 97), ("Eric", 1, 88), ("Jessica", 1, 98), ("Mike", 1, 82), ("Jeff", 1, NULL),
    ("Ben", 2, 93), ("Andrew", 2, 82), ("Jason", 2, 87), ("Carol", 2, 99), ("Fred", 2, 79);

SQL offers a wide range of aggregate functions to handle various data analysis tasks. The SUM function calculates the sum of values in a column, while AVG computes the average. COUNT counts the number of rows, and MAX and MIN find the maximum and minimum values, respectively. These functions form the foundation of data aggregation and analysis.

--Group By Example Cont.
CREATE TABLE grades (name TEXT, class INTEGER, grade INTEGER);

INSERT INTO grades (name, class, grade) VALUES
    ("John", 1, 97), ("Eric", 1, 88), ("Jessica", 1, 98), ("Mike", 1, 82), ("Jeff", 1, NULL),
    ("Ben", 2, 93), ("Andrew", 2, 82), ("Jason", 2, 87), ("Carol", 2, 99), ("Fred", 2, 79);

.mode column
.headers on
SELECT class, GROUP_CONCAT(name), AVG(grade)
FROM grades
GROUP BY class;

Aggregate functions can be combined with other SQL clauses, such as GROUP BY and HAVING, to perform advanced data analysis. The GROUP BY clause allows us to group rows based on specific columns, and aggregate functions can then operate on each group separately. This allows us to perform calculations and comparisons at different levels of granularity.

Aggregate functions Exercise Solution

--Code Completed
CREATE TABLE grades (name TEXT, class INTEGER, grade INTEGER);

INSERT INTO grades (name, class, grade) VALUES
    ("John", 1, 97), ("Eric", 1, 88), ("Jessica", 1, 98), ("Mike", 1, 82), ("Jeff", 1, NULL),
    ("Ben", 2, 93), ("Andrew", 2, 82), ("Jason", 2, 87), ("Carol", 2, 99), ("Fred", 2, 79);

SELECT class, MAX(grade)
FROM grades
GROUP BY class;

Aggregate functions in SQL are essential tools for data analysis and summarization. By utilizing these functions, we can perform calculations on groups of rows, obtain meaningful insights, and make informed decisions based on the summarized data. Understanding and harnessing the power of aggregate functions empowers us to streamline complex calculations and derive valuable information from our relational databases.