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:
|AVG()||the mean average of the elements in the column|
|COUNT()||the total number of elements in the column|
|DISTINCT()||the number of distinct values across the column|
|MAX()||the largest-value element in the column|
|MIN()||the smallest-value element in the column|
|SUM()||the arithmetic total of all values in the column|
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 ;
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.
SQL Sample Table Code
CREATE TABLE les_halles_menu ( item_fr VARCHAR(99), item_en VARCHAR(99), price DECIMAL ); 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) ;