Counting data

1. Counting data

Now that we can see what unique values exist in our data, let's learn how to count what we find.

2. The COUNT function

Previously, we discovered the different categories within our products range. This time, we're going to explore the scale of our data and answer "how many" questions. With this, we'll understand how much data we're working with. Let's bring up the products table again. When we ask "How many products do we have?" the AI assistant will include a COUNT function. The AI assistant will oftentimes recommend an alias as well, here it recommended total_count.

3. The COUNT function

Without this alias, the results will contain an unclear name using question mark characters.

4. The COUNT function

Instead of a question, we could also state, "Count the number of rows in the products table". But there's an important distinction to understand here.

5. Counting records or values

If we ask something like "How many products do we have?", the AI will likely use COUNT with an asterisk, written as COUNT all. This counts every single record, or row, in the table, regardless of whether individual fields have data or not. It's asking "how many records exist?". However, if you ask "How many product names do we have?" the AI uses COUNT with a specific field name, like COUNT(product_name). This only counts records where that particular field contains data, ignoring any empty cells. The difference matters when our data has gaps. As we see here, one of our products is missing a name. Both answers return correct queries, but they are different because they answer different questions.

6. Counting unique values

Now we can combine our counting skills with the DISTINCT keyword. When we ask "How many different categories do we sell?" the AI combines COUNT with DISTINCT. This counts only the unique values, not every occurrence, showing a count of 9.

7. To recap

This is powerful for data exploration. The AI assistant understands these nuances based on how we phrase our prompts. "How many records" or "how many rows" typically returns COUNT all. "How many product names" or "how many customers" usually generates COUNT(field). Adding words like "different," "unique," or "distinct" combines COUNT with DISTINCT. Counting gives us immediate insights into our data, helping us understand how much data we are working with and identifying any gaps.

8. Let's practice!

Let's practice using these counting capabilities.

Create Your Free Account

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