7.3 Aggregate functions and grouping

Aggregate functions in SQL are powerful tools for summarizing data. They allow you to count rows, calculate sums and averages, and find minimum and maximum values. These functions are essential for analyzing large datasets and extracting meaningful insights.

Grouping and filtering enhance the capabilities of aggregate functions. The GROUP BY clause organizes data into categories, while HAVING filters grouped results. Together, they enable complex data analysis and help uncover patterns within your database.

Aggregate Functions

Counting and Summing Data

Top images from around the web for Counting and Summing Data

• 

  Table-Valued Functions in SQL Server - CodeProject View original

  Is this image relevant?

• 

  Learn SQL Dates and Times with examples View original

  Is this image relevant?

• 

  SQL Abstract Syntax Trees Vocabulary View original

  Is this image relevant?

• 

  Table-Valued Functions in SQL Server - CodeProject View original

  Is this image relevant?

• 

  Learn SQL Dates and Times with examples View original

  Is this image relevant?

1 of 3

Top images from around the web for Counting and Summing Data

• 

  Table-Valued Functions in SQL Server - CodeProject View original

  Is this image relevant?

• 

  Learn SQL Dates and Times with examples View original

  Is this image relevant?

• 

  SQL Abstract Syntax Trees Vocabulary View original

  Is this image relevant?

• 

  Table-Valued Functions in SQL Server - CodeProject View original

  Is this image relevant?

• 

  Learn SQL Dates and Times with examples View original

  Is this image relevant?

1 of 3

• COUNT function returns the number of rows that match the specified criteria
  • Can be used with the DISTINCT keyword to count only unique values (COUNT(DISTINCT column_name))
  • Example:

    [SELECT](https://www.fiveableKeyTerm:select) COUNT(*) [FROM](https://www.fiveableKeyTerm:from) employees;

    counts the total number of rows in the employees table
• SUM function calculates the sum of a set of values
  • Ignores NULL values in the column
  • Example:

    SELECT SUM(salary) FROM employees;

    calculates the total sum of all salaries in the employees table

Calculating Averages, Minimums, and Maximums

• AVG function calculates the average value of a set of values
  • Ignores NULL values in the column
  • Returns a floating-point value
  • Example:

    SELECT AVG(price) FROM products;

    calculates the average price of all products
• MIN function returns the smallest value in a set of values
  • Can be used with numeric, string, or date data types
  • Example:

    SELECT MIN(hire_date) FROM employees;

    returns the earliest hire date among all employees
• MAX function returns the largest value in a set of values
  • Can be used with numeric, string, or date data types
  • Example:

    SELECT MAX(salary) FROM employees;

    returns the highest salary among all employees

Grouping and Filtering

Grouping Data with GROUP BY

• GROUP BY clause is used to group the result set by one or more columns
  • Typically used in conjunction with aggregate functions (COUNT, SUM, AVG, MIN, MAX)
  • Groups rows that have the same values in the specified columns
  • Example:

    SELECT department, COUNT(*) FROM employees GROUP BY department;

    counts the number of employees in each department
• GROUP BY clause must include all non-aggregated columns in the SELECT statement
  • Columns not included in the GROUP BY clause must be used with an aggregate function
  • Example:

    SELECT department, AVG(salary) FROM employees GROUP BY department;

    calculates the average salary for each department

Filtering Grouped Results with HAVING

• HAVING clause is used to filter the results of a GROUP BY query based on a specified condition
  • Similar to the WHERE clause, but used specifically with aggregate functions and GROUP BY
  • Filters the grouped results after the aggregation has been performed
  • Example:

    SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

    returns the departments with an average salary greater than $50,000
• HAVING clause can include aggregate functions in its conditions
  • Allows for filtering based on calculated aggregate values
  • Example:

    SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

    returns the departments with more than 10 employees