Lesson 10 – Grouping Data using GROUP BY & HAVING Clause

The GROUP BY clause is used in combination with the aggregate functions to group the result of the aggregate function with respect to unique values in another column. For instance, GROUP BY clause in combination with SUM aggregate function can help you calculate the sum of prices of all the products with respect to categories.

GROUP BY Clause

Let’s see the GROUP BY clause in action:

SELECT 
category_id, SUM (price) as TOTAL_PRICE FROM products
GROUP BY category_id

Here we first select the category_id column and then select the sum of prices from the products column, grouped by category_id. Here is the output:

Let’s see another example of GROUP BY where we will calculate the average price of all the products, but this time we will group the products by “instock” column. Look at the following script:

SELECT 
instock, AVG (price) as Average_Price FROM products
GROUP BY instock

Here is the output:

HAVING Clause

What if you want the total price of all the prices that are in stock? You might have guessed that the WHERE clause is the answer. But No! With GROUP BY clause, you cannot you use the WHERE clause, rather you have to use the HAVING clause as shown in the following example:

SELECT 
instock, AVG (price) as Average_Price FROM products
GROUP BY instock
HAVING instock == "yes"

If you run the above script, you should see the following output:

Let’s see another example of the having clause. This time we will see the maximum price value of the products in categories 1 and 2. Here is the script.

SELECT 
category_id, MAX (price) as MAX_Price FROM products
GROUP BY category_id
HAVING category_id == 1 OR category_id == 2

Here is the output of the above script:

What’s Next?

With this, you have completed the Introduction to SQL course. In this course, you have studied the most basic SQL concepts of the SQL language. However, this is just the tip of the iceberg. From here, I would suggest that you study how to connect a database with a software application developed in C#, Java, Python or any other programming language.