Aggregate Functions

• Databases


  A structured collection of data that is organized and stored in a way that allows for efficient retrieval, management, and manipulation of information
• Structured Query Language (SQL)


  A programming language used for managing data stored in relational databases
• Aggregate Functions


  • MySQL built-in functions that perform a calculation on multiple values and return a single value
  • Essential for database analysis, enabling quick insights into large datasets and for reporting tasks
• Examples of Aggregate Functions


  • total sales calculations
  • average score in a class
  • Richest Man in Northern Ireland
• Commonly Used Aggregate Functions


  • COUNT() - returns the number of records
  • AVG() - computes the average of a set of values
  • SUM() - adds together all values in a column
  • MIN() - returns the smallest value of the selected column
  • MAX() - returns the largest value of the selected column
• COUNT()


  The COUNT() function allows you to count all rows (COUNT(*)) or count only rows that match a specified condition (COUNT(column_name))
• The COUNT() function has forms like COUNT(*) FROM..., COUNT(*) FROM...WHERE..., COUNT(column_name) FROM..., COUNT(column_name) FROM...WHERE...
• SELECT COUNT(*) FROM...


  Counts the number of rows in a table
• SELECT COUNT(column_name) FROM...


  Counts the number of non-null values in the specified column
• Counting PetID or Name gives the same output count when filtering by Type = 'Cat'

  
• AVG()


  The AVG() function computes the average of a set of values
• Columns in Pet table


  • PetID
  • Name
  • Type
  • Breed
  • Age
• SELECT AVG (column_name) FROM…
• SELECT AVG (Age) FROM Pet;

  
• SELECT AVG (column_name) FROM…WHERE…


  Calculates the average value of the specified column from the table, with an optional WHERE clause to filter the rows
• SELECT AVG (Age) FROM Pet WHERE TYPE = 'Dog';

  
• SELECT SUM (column_name) FROM…


  Calculates the total sum of the values in the specified column from the table
• SELECT SUM (Age) FROM Pet;

  
• SELECT SUM (column_name) FROM…WHERE…


  Calculates the total sum of the values in the specified column from the table, with an optional WHERE clause to filter the rows
• SELECT SUM (Age) FROM Pet WHERE TYPE = 'Dog';

  
• MAX ( )


  Returns the maximum value in the specified column
• MIN ( )


  Returns the minimum value in the specified column
• SELECT MAX(Age) FROM Pet;

  
• SELECT MIN(Age) FROM Pet;

  
• SELECT Name, Age FROM Pet WHERE Age =(SELECT MIN(Age) FROM Pet);