The Group By clause in SQL organizes output into logical groups based on a variable. Once variables are grouped, we can then SELECT different things based on those groups, like the COUNT of a group, the MAX or MIN of a group, or the AVG of a group.
Let’s first consider some examples using qualitative data. For example, here is some data on the different customers and their respective regions.
SELECT CompanyName, Region
FROM Customers
LIMIT 10| CompanyName | Region |
|---|---|
| Alfreds Futterkiste | Western Europe |
| Ana Trujillo Emparedados y helados | Central America |
| Antonio Moreno Taquería | Central America |
| Around the Horn | British Isles |
| Berglunds snabbköp | Northern Europe |
| Blauer See Delikatessen | Western Europe |
| Blondesddsl père et fils | Western Europe |
| Bólido Comidas preparadas | Southern Europe |
| Bon app’ | Western Europe |
| Bottom-Dollar Markets | North America |
It is just a long list, not super useful. We already know that we could count the number of customers in each region with geom_bar().
SELECT CompanyName, Region
FROM Customersggplot(data = myquery,
aes(y = Region)) +
geom_bar(fill = "lightblue")
Sometimes you don’t want a graph though, you need a number. This is why you need to know to group and count. You could try to read these off the graph I guess, but only knowing how to produce one geom_bar() is pretty limiting. Let’s look at how to create the numbers that were used to generate the graph.
The COUNT function and GROUP BY clause are SQL’s primary tools for summarizing data. GROUP BY, instructs the database to slice that giant dataset into distinct buckets based on shared values in a specific column.
Once the data is sorted into these buckets, COUNT steps in to calculate the total number of rows within each individual group. For example, if you group an orders table by customer_id, the database aggregates the rows for each unique customer and uses COUNT to output exactly how many purchases each individual made.
Here is the same query, but now grouped to produce the same numbers that determine the size of the bars in the previous geom_bar() graph.
SELECT COUNT(*), Region
FROM Customers
GROUP BY Region| COUNT(*) | Region |
|---|---|
| 2 | NA |
| 8 | British Isles |
| 5 | Central America |
| 1 | Eastern Europe |
| 16 | North America |
| 4 | Northern Europe |
| 3 | Scandinavia |
| 16 | South America |
| 10 | Southern Europe |
| 28 | Western Europe |
Notice how the only changes I made to the code involved adding a GROUP BY (always after any WHERE statements) and instead of only SELECTing the CompanyName’s I now select the COUNT(*). This represents the number of rows in the data that correspond to each group.
COUNTING and GROUPING makes some weird variable names. Graphing something like COUNT() can be hard, so I highly recommend adding a quick alias (eg. COUNT(*) AS “n” or something similar). I like to use “n” to represent the number of rows in each group because it is used frequently in statistics for this purpose.
Grouped data can be filtered using the HAVING clause. It works like a WHERE clause, but for groups when you want to filter based on an attribute possessed by THE GROUP. Importantly, a WHERE filter will not override a HAVING. The order of the commands is also not interchangeable. WHERE comes first, then GROUP BY, then HAVING.
SELECT COUNT(*) AS n, Region
FROM Customers
GROUP BY Region
HAVING n > 10Look at the difference between the output of these two queries. Can you see how HAVING has altered the results?
SELECT COUNT(*) AS n, Region
FROM Customers
GROUP BY Regionggplot(data = myquery1,
aes(y = Region, x = n)) +
geom_col(fill = "lightblue") +
labs(title = "Plot showing all customers in all regions")
SELECT COUNT(*) AS n, Region
FROM Customers
GROUP BY Region
HAVING n > 10ggplot(data = myquery2,
aes(y = Region, x = n)) +
geom_col(fill = "lightblue") +
labs(title = "Plot with data filtered using HAVING n > 10")
When we count and group ourselves, we can no longer rely on geom_bar() to produce our counts. We must explicitly tell GGPLOT which column contains our variable names, and which column contains our counts. We then have access to some new geoms. It is like a bar chart, but for data that has already been summarized. I use geom_col() in the two examples above. Also, notice that I graph the name of my alias (n), not COUNT(*).
You can also use two groupings together with fill inside a geom_col to create stacked bar charts like we saw in Week 2. Here I select both customer cities and regions and group by BOTH of these, before creating a stacked geom_col(). Now we can see which cities are best represented in each region.
SELECT COUNT(*) AS n, Region, City
FROM Customers
GROUP BY Region, Cityggplot(data = myquery3,
aes(y = Region, x = n, fill = City)) +
geom_col()
That’s too many cities to be useful. Let’s filter it using HAVING.
SELECT COUNT(*) AS n, Region, City
FROM Customers
GROUP BY Region, City
HAVING n > 2ggplot(data = myquery3,
aes(y = Region, x = n, fill = City)) +
geom_col() +
labs(title = "Graph showing regions and cities HAVING n > 2")
Note that if you have multi-group pairings in your code, each multi-group must meet the HAVING criteria. In this case, HAVING n > 10 would filter out all of the data, as there are no cities that have more than 10 customers. For this reason, I used HAVING n > 2. The graph shows all region/city pairings that have more than two customers
These next two geoms are entirely optional, but if you have an idea you’d like to try to get working, just reach out to me!
Here is a sleek looking alternative to a geom_col(). It uses two separate geoms, a line segment and a geom_point to create a similar looking aesthetic. You only need to change the variable names for the x, y , xend and yend to get this to work for your own data.
ggplot(data = myquery5,
aes(x = n, y = Region)) +
geom_segment(aes(x = 0, xend = n, y = Region, yend = Region),
color = "gray70", linewidth = 1) +
geom_point(color = "#2c3e50", size = 4) +
theme_minimal(base_size = 12) 
Heatmaps allow you to visualize the concentration of various 2x2 combinations of variables. They offer another way to visualize information from a stacked bar chart. The example below shows how many Customers are located in each Country in Europe. Darker squares indicate higher customer concentrations.
ggplot(myquery6, aes(y = Country, x = Region, fill = n)) +
geom_tile(color = "white", linewidth = 0.5) +
scale_fill_gradient(low = "#e0f2fe", high = "#0369a1") +
theme_minimal(base_size = 12) 
Counting is great for things that aren’t quantitative numbers. If you have quantitative variables, you can use different functions including SUM(), AVG, MAX etc. Here, I again look at the Product reorder level variable we considered in Week 2, grouped by Category. The data is filtered to just include the Produce and Beverages categories.
SELECT CategoryName, Reorderlevel,
SUM(CAST(reorderlevel as INTEGER)) as total_reorder,
MAX(CAST(reorderlevel as INTEGER)) as biggest_reorder,
MIN(CAST(reorderlevel as INTEGER)) as smallest_reorder,
AVG(CAST(reorderlevel as INTEGER)) as avg_reorder
FROM Products
INNER JOIN Categories
ON products.CategoryID = Categories.CategoryID
WHERE CategoryName IN ("Produce","Beverages")
GROUP BY CategoryName| CategoryName | ReorderLevel | total_reorder | biggest_reorder | smallest_reorder | avg_reorder |
|---|---|---|---|---|---|
| Beverages | 0 | 195 | 30 | 0 | 16.25 |
| Produce | 0 | 25 | 10 | 0 | 5.00 |
Compare the table above to boxplots of the same data. Can you find the min, max and average values on the boxplots?
