Grouping data

3.1 GROUP BY statement

The SQL language is very powerful. In addition to simple data fetching, it allows you to group data at query time. For example, we want to know how many kinds of products of each brand we have, how can we do that?

There is a special operator for this GROUP BY, here is how it is written.

GROUP BY column

Let's write a query that asks SQL to display the number of product types for each brand. Such a request would look something like this:

SELECT brand, COUNT(*)
FROM product
GROUP BY brand

You will get the following query result:

Let's carefully analyze what kind of request we wrote, and what kind of result we got. As a result, we see two columns. The first with a list of brands, which is logical. We wanted to get something like a set of pairs "brand --> quantity" .

Please note that among the brand names there is a word NULL. Indeed, we have products in the brand in which it is written NULL, and we see them here. A little unexpected, but helpful. We can see how many unbranded products there are.

Now let's take a closer look at the request. We wanted to know the number of products of each brand, so we wrote GROUP BY brand, and in SELECTwe wrote brand and the expressionCOUNT(*)

COUNT(*)is a so-called aggregate function that is performed on a group of rows. In our case, she simply counts their number. There are a lot of such functions, and we will look at them a little later.

3.2 Complex queries with GROUP BY

Now let's write a query where we first discard products whose price is less than 10, and then group them. Such a request would look something like this:

SELECT brand, COUNT(*)
FROM product
WHERE price > 10
GROUP BY brand

You will get the following query result:

All NULL products disappeared from our results table, as they were very cheap, and the number of IKEA products decreased by 1. From 6 to 5.

This suggests that you first apply WHEREthe desired lines to filter, and only then apply the grouping to the result.

3.3 Column names of the result

By the way, you can give names to the resulting columns. See how the server suggested the name count(*)? Let's replace it with total. Such a request would look something like this:

SELECT brand, COUNT(*) AS total
FROM product
WHERE price > 10
GROUP BY brand

You will get the following query result:

You can also rename existing columns. Let's take one of the previously used queries and add new names to the columns.

SELECT
id AS  product_id,
name AS product_name,
price
FROM product
WHERE price < 20 AND brand IS NOT NULL

You will get the following query result: