Aggregate Functions

Aggregate functions allow you to group queried data in meaningful ways. SQL databases are more than just simple data stores; the complex functionality of SQL allows you to extract meaningful data very easily.

A common aggregate function is GROUP BY. This function allows you to perform operations on groups of data. Let’s add some more data to our example database and see what options GROUP BY gives us.

mysql> insert into families set father_name = "Adam White", 
-> father_dob = "1969-06-08", mother_name = "Tina White",
-> mother_dob = "1969-01-30", number_of_children = "1",
-> child1_name = "Ed White", child1_dob = "1998-11-17",
-> notes = "This is the White family of Bellevue, NE",
-> city = "Bellevue", state = "NE";
Query OK, 1 row affected, 0 warnings (0.00 sec)
mysql> insert into families set father_name = "Bill Carpenter",
-> father_dob = "1968-06-01", mother_name = "Linda Carpenter",
-> mother_dob = "1970-02-30", number_of_children = "4",
-> child1_name = "Joe Carpenter", child1_dob = "1998-12-17",
-> child2_name = "Bob Carpenter", child2_dob = "1996-01-01", 
-> child3_name = "Luke Carpenter", child3_dob = "2004-08-08",
-> notes = "This is the Carpenter family of Lincoln, NE",
-> city = "Lincoln", state = "NE";
Query OK, 1 row affected, 0 warnings (0.00 sec)

mysql> select id, father_name, city, state from families; +----+----------------+----------+-------+ | id | father_name | city | state | +----+----------------+----------+-------+ | 1 | Joe Smith | Chicago ...

Get LPI Linux Certification in a Nutshell, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.