SQL Group By — Refining Your Queries

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. SQL Select retrieves those rows that satisfy the search criteria.

SQL Group By — along with Having and Order By — gives you the power to fine-tune the results, making it easier to deliver actionable data directly, without further massaging before sharing.

Let’s start with perfumeries, a large table of fragrance houses worldwide (the source to make the table appears at the bottom of this post). Here are the first few rows:

Using SQL Where to Narrow Results

We can, for example, get all the houses in a given country by:

SELECT * FROM perfumeries
WHERE country = "Argentina" ;

Getting all of them from a region — say North America — is not much more involved when we use the WHERE IN clause-operator pairing:

SELECT * FROM perfumeries
WHERE country IN ( "Canada", "United States", "Mexico" ) ;

SQL Group By Makes Lists

Let’s pivot, though, and start looking at the data in the aggregate. Perhaps we’re interested in improving import/export flow and we should target our legislative efforts at those countries that have the most fragrance houses. Since we’re not interested in countries that don’t have any fragrance houses, let’s generate a simple list of those which do. SQL Group By gives us a first step in this direction:

SELECT country FROM perfumeries
GROUP BY country ;

This removes all duplicates from the results, the first few rows of which looks like:

SQL Group By and count(column)

A count of the number of fragrance houses in each country would help target our attention.

SELECT country, COUNT(house) FROM perfumeries
GROUP BY country ;

The syntactic magic contained in COUNT(house) is worth a moment’s pause. SQL will count all the elements specified and return the sum. The first few rows of the result look like::

Adding SQL Order By Is More Actionable

A good start, the countries in alphabetical order isn’t exactly what was wanted. Using SQL Order By will refine the list further (because asking to group by COUNT(house) is syntactically invalid):

SELECT country, COUNT(house) FROM perfumeries
GROUP BY country
ORDER BY COUNT(house) DESC ;

DESC means “descending order,” ASC is “ascending order.” The first few lines of the resulting table look like:

Use SQL Having to Extract Exactly

All the examples above show the first lines of a much longer results table. Let’s get at exactly the data we want and remove all human post-processing from our code. This will (1) remove a source of human exhaustion and error and (2) make our code ready for participating in a business logic workflow.

SELECT country, COUNT(house) FROM perfumeries
GROUP BY country
HAVING COUNT(house) >= 5
ORDER BY COUNT(house) DESC ;

The above code delivers exactly the following table:

Use SQL Where Like for Even Greater Granularity

To end, in the spirit of having our SQL return exactly what’s needed for the task at hand (without any post-processing), a moment taken with pattern-matching wildcards is in order. Consider this refinement:

SELECT country FROM perfumeries
WHERE country LIKE 'K%'
GROUP BY country ;

The WHERE LIKE clause-operator pairing — country LIKE 'K%' — further restricts the results set to those countries which match the pattern “begins with the letter K.” Pattern-matching is a rich subject and I hope to share the power unlocked within in a future blog post.

Conclusion

SQL Group By — coupled with its cousins Having, Order By, Where In, and Where Like — along with related SQL functions — like COUNT(column) — enables you to fine-tune results, making it easier to filter through and deliver actionable data directly to your audience, whether that be human readers or another program waiting for input, without the time-consuming and error-prone human post-processing.

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

Start Learning

SQL Group By Sample Table Code

The following SQL will build and populate a tiny subset dataset  into the sample table used in this article. Too large for SQL Fiddle, the full dataset is provided should you need an accurate, real-world collection of location-based information.

CREATE TABLE perfumeries (
    country VARCHAR(33),
    house VARCHAR(50) ) ;
INSERT INTO perfumeries
VALUES
( "Argentina", "Arkadia Sabores y Aromas de Autor" ), ( "Argentina", "Frassai" ), ( "Armenia", "Arman Manoukian Parfums" ), ( "Armenia", "Voskanian Parfums" ), ( "Aruba", "Agatha" ), ( "Austria", "Acqua Alpes" ), ( "Austria", "Alt-Innsbruck" ), ( "Austria", "ANNO 1555" ), ( "Austria", "CnR Create" ), ( "Azerbaijan", "Crystal Parfum" ), ( "Azerbaijan", "Murad Mesud" ), ( "Azerbaijan", "The Spirit Of Azerbaijan" ), ( "Bahrain", "Asgharali" ), ( "Bahrain", "Reehat Al Atoor" ), ( "Bahrain", "Shaik" ), ( "Bahrain", "Syed Junaid Alam" ), ( "Belarus", "Dilis Parfum" ), ( "Belarus", "Ninel Perfume" ), ( "Belgium", "Les Ecuadors" ), ( "Belgium", "Les Soeurs de Noe" ), ( "Belgium", "NeZ ZeN" ), ( "Belgium", "Nico Uytterhaegen" ), ( "Bermuda", "Lili Bermuda" ), ( "Bermuda", "Royall Lyme Bermuda" ), ( "Bulgaria", "Alen Mak" ), ( "Bulgaria", "Nature Of Agiva" ), ( "Bulgaria", "Rose of Bulgaria" ), ( "Bulgaria", "Sense of Nature" ), ( "Cayman Islands", "Ted Green Cayman Islands" ), ( "Chile", "Coral" ), ( "Chile", "Cristian Brinck" ), ( "Chile", "Natalie" ), ( "Chile", "Terragonia" ), ( "China", "Boitown 冰希黎" ), ( "China", "LiuShen 六神" ), ( "Columbia", "Jean Pascal" ), ( "Columbia", "The Lab" ), ( "Cote D’Ivoire", "Olfacto" ), ( "Croatia", "Macal Palma" ), ( "Croatia", "Majushka" ), ( "Cuba", "Agustin Reyes" ), ( "Cuba", "Suchel Camacho" ), ( "Cuba", "Vegueros" ), ( "Czech Republic", "S.A.C.K.Y" ), ( "Denmark", "Henrik Vibskov" ), ( "Denmark", "RPL" ), ( "Denmark", "Royal Copenhagen" ), ( "Denmark", "Skandinavisk" ), ( "Denmark", "Tromborg" ), ( "Dominican Republic", "Dominican Perfumes" ), ( "Dubai", "I-Scents Premium" ), ( "Ecuador", "Nino Touma" ), ( "Ecuador", "Roberto Manrique" ), ( "Egypt", "Chabrawichi" ), ( "Egypt", "Nilafar du Nil" ), ( "Egypt", "Omar Sharif" ), ( "Egypt", "Parfico" ), ( "England", "Prosody London" ), ( "England", "Bamford" ), ( "Estonia", "Flora" ), ( "Finland", "Nakuna Helsinki" ), ( "Georgia", "Iberia - Иверия" ), ( "Ghana", "Ghandour" ), ( "Greece", "Manos Gerakinis" ), ( "Greece", "Mastiha Shop" ), ( "Guatemala", "INVISIBLE/HR" ), ( "Hong Kong", "Aurora par Charles Wong" ), ( "Hong Kong", "Cathay Pacific Airways" ), ( "Hungary", "Mar Galliti" ), ( "Hungary", "Omorovicza" ), ( "Iceland", "Gydja" ), ( "India", "Oudh Al Anfar" ), ( "Indonesia", "Animale" ), ( "Iran", "Aqua Perfume Atelier" ), ( "Iran", "Viva Creation" ), ( "Ireland", "Cloon Keen Atelier" ), ( "Israel", "Ein Gedi" ), ( "Israel", "Ga-De" ), ( "Kazakhstan", "Aura of Kazakhstan" ), ( "Kenya", "Lheritier" ), ( "Korea", "The Face Shop" ), ( "Kuwait", "Mahdi Alajmi" ), ( "Kuwait", "Ne'emah For Fragrance & Oudh" ), ( "Latvia", "Dzintars" ), ( "Latvia", "Stenders" ), ( "Liberia", "Tammie Garr" ), ( "Lithuania", "Eglė Jonaitytė Botanical Perfumes" ), ( "Lithuania", "Juozas Statkevicius Josef Statkus" ), ( "Luxembourg", "Mypa" ), ( "Malaysia", "Auphorie" ), ( "Malaysia", "Ningen Parfums" ), ( "Malta", "FWIEHA FRAGRANZA TA'MALTA" ), ( "Mexico", "Aguas de Colonia Sanborns" ), ( "Mexico", "Pedacito de Cielo" ), ( "Mexico", "Xinú" ), ( "Morocco", "Heritage Berbere" ), ( "Morocco", "Les Parfums du Soleil" ), ( "Morocco", "Les Sens de Marrakech" ), ( "New Zealand", "Karen Walker" ), ( "New Zealand", "Michael Hill" ), ( "Nigeria", "Catherine Omai" ), ( "Nigeria", "Sapphire Scents" ), ( "Norway", "Kanøn" ), ( "Pakistan", "Junaid Jamshed" ), ( "Peru", "Cyzone" ), ( "Peru", "L'Bel" ), ( "Peru", "Ésika" ), ( "Philippines", "Manny Pacman Pacquiao" ), ( "Philippines", "Radioactive Mushrooms in the Forest" ), ( "Portugal", "Antiga Barbearia de Bairro" ), ( "Portugal", "Aqua Dos Açores Atlantic Ocean" ), ( "Puerto Rico", "Exotic Island Aromas" ), ( "Qatar", "S.Ishira" ), ( "Romania", "Essence de Roumanie" ), ( "Romania", "Giulia Nahmany" ), ( "Rwanda", "Aqua Rwanda" ), ( "Scotland", "Jorum Studio" ), ( "Serbia", "Emina Jahovic" ), ( "Singapore", "VT x BTS" ), ( "Slovakia", "1907" ), ( "South Africa", "Hoity Toity" ), ( "South Africa", "Titanium Man" ), ( "Spain", "Label" ), ( "Sri Lanka", "La Signore The Fragrance" ), ( "St. Barts", "Calypso Christiane Celle" ), ( "Tahiti", "Parfumerie Tiki Tahiti" ), ( "Thailand", "Bortnikoff" ), ( "Thailand", "SIAM 1928" ), ( "Ukraine", "Karen Doué" ), ( "Ukraine", "Kharkov perfume factory" ), ( "Ukraine", "Mi6ka" ), ( "United States", "American Perfumer" ), ( "United States", "Harlem Botanica" ), ( "Uzbekistan", "Begim" ), ( "Uzbekistan", "Guli" ), ( "Vietnam", "Levinia" ), ( "Vietnam", "Saigon Cosmetics" ), ( "Vietnam", "Y25" ) ;