Grouping data

1. Grouping data

Sorting organized the data; now, let's group it.

2. Grouping data

When analyzing data, we often want insights about groups, not just individual items. Imagine our book collection. Looking at every book one by one doesn't reveal much about patterns. But if we group them by language, we can quickly see how many are in English, French, or Spanish. Grouping works the same way with data: it combines rows with the same value, like language, to create summaries that reveal patterns.

3. Distinct vs. groups

Let's see how this works with AI. Our data includes thousands of film titles across multiple languages. Depending on our prompt, an AI may generate a query using DISTINCT or GROUP BY. Both appear to remove duplicates, but with a key difference. DISTINCT returns the unique values, like a list of the languages in our database, while GROUP BY creates groups we can analyze further. Think of DISTINCT as answering "What languages do we have?" while GROUP BY sets us up for "How many films do we have in each language?".

4. Prompts for grouping

A direct prompt like "Group films by language" might produce a query that lists each language alongside a count of films. Notice that we didn't actually ask for counting in the prompt but because grouping is almost always paired with a summary, the AI includes a count to keep the results useful and readable. Different prompts may produce different queries, so always check and refine if needed. Here, the AI chose COUNT(), an aggregate function that summarizes multiple rows into a single value per group, like counting all Spanish films. Without GROUP BY, it summarizes the entire table.

5. Common grouping errors

Our query may return an error if we try to select a field that has not been grouped. AI assistants typically handle this error by again including a COUNT() function or including the correct fields to select.

6. Grouping prompts

The key to reliable GROUP BY generation is a clear prompt, like "Count films by country and language". Similar to sorting, grouping can also work on multiple fields, grouping by the first field listed, then the second, and so on. Here, we see one film is missing values for both country and language and there are three English-language films from Mexico.

7. Grouping and sorting

Finally, let's combine grouping with sorting. "Rank languages by number of films, showing the top 3" generates a query that selects, groups, orders by the count, then limits the results, producing one row per language, sorted from highest to lowest. This gives us a clear view of our most common languages. We can verify that our grouping worked by checking that each language appears only once in our results. Notice how the ORDER BY clause is using the film_count alias to sort. This is due to SQL's order of operations, which created the alias before the sorting occurred. We'll learn more about this later in the course.

8. Let's practice!

Next, we'll explore more aggregate functions to unlock further insights from our grouped data. For now, let's head to the exercises and practice grouping.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.