SQL Essentials Part Two: IN, COUNT, And GROUP BY

In this post I'll demystify the IN operator, unravel the power of the COUNT function, and explore strategic data grouping with GROUP BY!

Welcome to my blog post where we embark on a journey into the intricacies of SQL, unraveling the potency of the IN operator, delving into the functionality of the COUNT function, and exploring the strategic use of the GROUP BY function. In this exploration, we aim to provide practical insights and examples to elevate your database skills. Whether you're a seasoned developer or just starting, join us in unlocking the full potential of these SQL concepts for effective data manipulation and analysis.

IN Operator

In the previous post, we touched on the WHERE clause and how it allows you to select only certain pieces of data. You can recall that by using a WHERE clause, we can write a query that allows us to select only certain pieces of information. For example, we saw how we could write a query that would allow us to return a list of customers from the city of London.

That query returns the following information.

Now, what if we needed to include another city in the list? How would we go about writing a query that would return a list of customers from the cities of London and Berlin? Well, we could use an OR statement to add another conditional to the WHERE clause.

In this query, by using the OR statement, we are checking to see that the customer's city is either London OR Berlin, as opposed to both London AND Berlin.

Now, while this query returns the correct information, we can enhance our query by using a the IN operator , which is used to specify multiple OR values and is used as a shorthand for doing so. With this in mind, we can rewrite our query to include an IN operator in place of the OR statement.

And just like that, we've written a query that utilizes the IN operator for cleaner and easier-to-read code. But that's not all; by using the NOT operator in front of the IN operator, we can now exclude multiple OR values. For example, let's say that we need a list of all our customers except for the ones located in Mexico, Spain, or Germany. By utilizing the NOT operator, we can write a query that will do just that

Now let's take it a step further. Say you need a list of customers only from the countries of Mexico and Spain; however, you want to exclude any customers from the city of Madrid. Now we need to use both the IN and NOT IN operators in the same query, but how would we go about doing so? Well, by separating both operators using an AND statement, we can write a query that looks like this.

As you can see, this query returns a list of all the customers from Mexico and Spain without including any from the city of Madrid!

And just like that, we're able to combine multiple operators to retrieve a very specific set of information by writing efficient and easy-to-read queries!

Aggregate Functions

Up until now, we've seen how we can write queries to retrieve all sorts of information from a database. Now, while that is an important part of SQL, that's not all you can do with SQL. In the next part of SQL Essentials, we will begin to look into what is known as aggregate functions.

An aggregate function in SQL is a function that allows us to take data from a column and perform computations on it to return a single value. The first of these functions we will look into is the COUNT function.

COUNT 

In SQL, the COUNT function does what its name implies. That is, it allows us to count how many records there are that match its conditions. Say, for example, you need to count the number of customers you have. How would you use the COUNT function in a query to retrieve that information? Well, by writing a query like this, we can do just that.

The query returns the following information.

As we can see, we have now retrieved the total amount of customers in our database as a single value, but you may have noticed that the result is in an unnamed column. Well, by adding AS and the desired name after our COUNT function, we are able to add a name to the column that our query returns.

Now our results come in a column named Customers.

Now that we have a general understanding of the COUNT function, let's take things a step further. Say we need a count of all of our orders in a column named Orders but only from the orders where the ship country is in Italy, France, or Spain. Now we need to find a way to add a conditional with multiple OR values in addition to our COUNT function. Well, by using a WHERE clause and IN operator, we can craft a query that looks like this.

This query yields the following results

And just like that, we were able to use the COUNT function in conjunction with a WHERE clause to count a specific set of information.

Up until now, we've been able to use the COUNT function to aggregate all the records in a whole table. Now we'll look into how we can aggregate parts of a table.

GROUP BY

The GROUP BY statement is used to aggregate parts of a table as opposed to the whole column. For example, we need to write a query that will count all of the orders from Italy, France, and Spain, but we would like the results to be separated by each country. That means we would need to get 3 separate values from our query. In order to get the desired results, we would have to write a query that will not only meet a conditional but also separate the results from our aggregate. Well, by using the GROUP BY statement, we can write a query that will do just that.

And as you can see, our results come back separated by the three countries we specified in our WHERE clause.

Let's take it one small step further and write a query that will count the amount of products each of our suppliers in our database has separated by the supplier id and in order of most to least products. Now our query requires that we ORDER BY the result of our aggregate function but how would we go about doing that? Well, remember how by using AS after our aggregate we can assign a column name to that value? By using the column name we assigned to our aggregate, we can now use the ORDER BY statement in conjunction with the GROUP BY statement to separate the amount of products each of our suppliers has in descending order.

And as you can see, the results are in descending order by the ProductCount column with the name we assigned to it!

Wrapping It Up

In this post, we expanded on our previous post by utilizing the IN operator to write shorter and easier-to-read queries. We were also introduced to aggregate function, more specifically, the COUNT function which allowed us to count the amount of records in a table. We also expanded on the COUNT function by using the WHERE clause to count only a specific set of data and by using the ORDER BY statement, we were able to aggregate parts of a table as opposed to the whole table. Hopefully, you're starting to get a grasp of the different things you can do with SQL. Stay tuned for future posts where we will continue to navigate through the SQL Essentials!