Answer to Question #194422 in Databases | SQL | Oracle | MS Access for oliver

Question

What is SQL Aggregate Functions Define with Examples

Answer 1

// some corrections

Aggregate functions calculate some scalar values ​​in a rowset.

MySQL has the following aggregate functions:

AVG: calculates the average

SUM: calculates the sum of values

MIN: calculates the smallest value

MAX: calculates the largest value

COUNT: calculates the number of rows in a query

For example, we can illustrate this on the table SouthAmericanParrots,

calculating the average number of the parrots species in different countries,

finding minimal and maximal number, total count of species and countries.

create table SouthAmericanParrots(id integer, country_name varchar(50),

number_of_species integer, most_common_species varchar(100));

insert into SouthAmericanParrots(id, country_name, 

number_of_species, most_common_species) 

values(1, "Brasilia", 78, "Ara macao"),

(2, "Peru", 38, "Golden-plumed parakeet"),

(3, "Argentina", 52, "Burrowing parrot"),

(4, "Equador", 43, "Amazon");

select * from SouthAmericanParrots;

SELECT AVG(number_of_species) AS Average_number_of_species FROM SouthAmericanParrots;

SELECT MIN(number_of_species) AS Minimal_number_of_species FROM SouthAmericanParrots;

SELECT MAX(number_of_species) AS Maximal_number_of_species FROM SouthAmericanParrots;

SELECT SUM(number_of_species) AS Total_number_of_species FROM SouthAmericanParrots;

SELECT COUNT(country_name) AS Total_number_of_countries FROM SouthAmericanParrots;