SQL Aggregate Functions — AVG, COUNT, DISTINCT, MAX, MIN, SUM

Relational databases store information in tables — with columns that are analogous to elements in a data structure and rows which are one instance of that data structure. The SQL language is used to interact with that database information.

The SQL aggregate functions — AVG, COUNT, DISTINCT, MAX, MIN, SUM — all return a value computed or derived from one column’s values, after discarding any NULL values. The syntax of all these functions is:

SELECT AGGREGATE_FUNCTION( 'column_name' )
FROM TABLE_NAME

The aggregate functions are:

We’ll examine the workings of the SQL aggregate functions by interacting with some menu items and prices from the former Brasserie Les Halles, perhaps best-known for it’s one-time executive chef, Anthony Bourdain. (The SQL needed to create and populate the tables are given at the bottom of this blog entry.)

AVG() — The Mean Average

The AVG() aggregate function returns the mean average of the numeric values in the specified column. For example,

SELECT AVG(price) FROM les_halles_menu ;

To constrain the returned result to two decimal places, as is typically expected for currency, use the FORMAT() function — FORMAT(AVG(price), '2') returns 17.47. To have the value formatted properly for a given locale use something like FORMAT(AVG(price), '2', 'fr_FR'), which returns 17,47 (as is expected in France) (The fr_FR is composed from ISO-3166 Country Codes and ISO-639 Language Codes.) Check the syntax for your chosen database implementation as many variations may be available to you.

COUNT — How Many Exist?

The COUNT() aggregate function returns the number of items in the column. For example, to count the number of the French-language menu items:

SELECT COUNT(item_fr) FROM les_halles_menu ;

DISTINCT — How Many Unique?

The DISTINCT() aggregate function returns the set of unique values in the column, discarding multiples. For example, to see exactly one of each of the prices appearing on the menu,

SELECT DISTINCT(price) FROM les_halles_menu ;

MAX & MIN — The Largest and Smallest

The MAX() aggregate function returns the largest value from the column. For example, to find the most expensive menu item:

SELECT MAX(price) FROM les_halles_menu ;

MAX() also works on text values; to find the last item in the names of the English-language menu items: 

SELECT MAX(item_en) FROM les_halles_menu ;

MIN() provides the same functionality, with a focus on the smallest values.

SUM — All Together Now

The SUM() aggregate function returns the sum of all the numeric values from the column. For example, to calculate the total cost of a dinner consisting of one of each menu item:

SELECT SUM(price) FROM les_halles_menu ;

Conclusion

We’ve covered the functionality and sample uses of each of the SQL Aggregate Functions — AVG, COUNT, DISTINCT, MAX, MIN, and SUM. While all of these functions return a value computed or derived from the specified column’s values, after discarding any NULL values, some of these functions work on both numeric and alphabetical values.

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

SQL Sample Table Code

The following will build the code sample tables referred to in this blog post. Examine this code interactively at SQL Fiddle or CodingGround.

CREATE TABLE les_halles_menu (
item_fr VARCHAR(99),
item_enVARCHAR(99),
priceDECIMAL
);
INSERT INTO les_halles_menu
VALUES
( "Croque-madame",
"Classic French ham and cheese sandwich with a fried egg on top", 16),
( "Confit de Canard",
"Duck leg confit, frisee salad and truffled potatoes", 25),
( "Coq au Vin",
"Rooster, red wine, bacon, button mushrooms and pearl onion", 26),
( "Côte de Porc au Choux Frisé en Salade",
"Grilled bone-in pork chop, kale salad, lardons, shoe-string potatoes, and maple-mustard glaze", 32),
( "Vivaneau rouge poêlé",
"Pan-roasted red snapper, roasted parsnips, carrot puree, coconut beurre blanc, and parsnip chips", 32),
( "Noix de Saint-Jacques poêlées au champagne",
"Pan-seared scallops with artichoke barigoule and champagne cream sauce", 30),
( "Saumon Grillé Tout Simple",
"Salmon, leek fondue, fingerling potatoes, and citrus sauce", 24),
( "Chou-Fleur Frit et en Purée",
"Fried cauliflower florets and cauliflower puree", 9),
( "Persillade de Champignons",
"Sauteed wild mushrooms, garlic, and parsley", 10),
( "Moules-frites",
"PEI mussels served with house made fries.", 10),
( "Frites parfumée à la truffe",
"Truffle Fries", 11),
( "Poutine joue de bœuf",
"Beef cheeks, red wine, award-winning French fries, cheese curds, and gravy", 16),
( "Crème Brûlée",
"Classic custard topped with a layer of hard caramel", 9),
( "Profiteroles à la crème et sauce chocolat",
"Cream puffs filled with vanilla ice cream, served with chocolate sauce", 8),
( "Bananes flambées",
"Caramelized bananas, flambeed with rum tableside", 15),
( "Crêpes Suzette",
"Crepes prepared and flambeed with grand mariner tableside", 15),
( "Mousse au chocolat",
"Chocolate Mousse with Valrhona chocolate", 9) ;