You're here because you want to learn how the GROUP BY feature in SQL works.
In this video, you'll learn what it is, how it works, and see a couple of examples.
Let's get into it.
So, what is GROUP BY?
It's a feature in SQL that lets you display your data in groups. It's used with an aggregate
function, which is a function that combines several rows of data into one row, such as SUM.
Using GROUP BY will let you see the number of sales per product, for example,
or the number of employees per department.
Let's see it on a sample table
Here's a table called orders. We have an order ID which is the unique identifer,
an order date which is when the order was placed, a product ID which refers
to the product in another table, and an order value which is the amount paid for an order.
Let's say we want to see the number of orders for each product. How would we do this?
We could write a query like this.
We select the order ID and the product ID and run the query.
We'll see just those two columns, but we'll see all rows in the table.
We could manually add them up to find our answers. We could export the results to Excel
to analyse or create a pivot table, or we could use a programming language to combine the rows.
There is a much easier way.
We can use an aggregate function to find what we need.
In this query, we select the COUNT of all orders.
If we run this query, we'll get a single row returned,
that shows all of the orders in the table.
This is the other kind of result we'll get.
We can see the number of orders, but it's the total orders, not the orders per product.
This is where the GROUP BY feature comes in handy.
The GROUP BY feature will let us say, "show us the count of orders, but group the count by each
product". It will let us show each product ID, and then the count of orders for that product ID.
Let's try it.
We start by selecting the field we want to display. In this case, it's product ID.
We then select the COUNT function as a second column.
Then we select from the table.
Next we add the GROUP BY clause after the FROM clause. We add in the GROUP BY keyword,
then we specify the column, or columns, we want to group by. This is saying, show us the count
of values for each group of "what field". We want to group our results by the product ID.
So, enter in product id after the group by.
A good way to remember this is that if you're selecting a field,
then you'll most likely want to group by it.
Now, when you run the query, you'll see these results.
We have the product ID and the count of orders for each product.
Exactly what we wanted!
The group by clause in our query has easily calculated
the count of each product ID record in the table.
You could even adjust this query slightly if you wanted to order your results.
For example, if you wanted to see the number of orders per product,
sorted by the products with the most orders, you can add an ORDER BY COUNT desc to your query.
The order by has to go at the end, after the Group By.
When you run the query, this is what you'll see. We see the same structure of the results,
so the same columns and COUNT, but the data is ordered by count.
Now, you may be thinking, why do I need the group by? Can't I just select the column and the count?
We can try that. If we go back to our query and remove the group by clause,
and the order by clause if you have it, your query would look like this.
We select the column and the count function from the table.
Let's try to run the query.
If you run the query, you'll get an error like this. The exact message will depend on your
database, but it's something like "column is not an aggregate function or in the group by clause".
Essentially, if you're using an aggregate function such as COUNT or SUM, any other column you have in
your select query that is not an aggregate function should be in the GROUP BY clause.
This is because the database needs to know what to do with the column.
Interestingly, in some versions of MySQL, a query like this would still run,
but give you incorrect results. So that's something to be aware of.
The group by clause also works with queries that use other aggregate functions.
We can try a query that uses the SUM function, which provides a total of a series of numbers.
We can find the sum of the order values for each product using this query.
Instead of count *, we are using SUM order value. We still group by the product ID.
If we run the query, this is what we'll see.
We can see each product ID, along with the sum of the order value column.
This is done using the group by clause.
So, that's how you use the group by feature in SQL.
And that brings us to the end of this video on GROUP BY in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which step from this tutorial was the most helpful? Was it the fact
you can use different aggregate functions, or that any field in the select clause not
in an aggregate function should be in the group by, or something else?
Thanks for watching.