# Objective

To learn how to group data using GROUP BY and HAVING clause in the SELECT statement.

# Why Group Data?

In the previous article you learned aggregate functions that performed calculations on all the data in a table or on data that satisfied a specific condition of the WHERE clause. We were able to count the number of products. If we want to count the number of products for each user we need to use grouping. Grouping allows us to divide data into logical sets and perform aggregate calculations on each group.

# Group By Clause

Let's count the number of products that belong to each user in the products table.

``````sqlite> select user_id, count(*) as product_count from products group by user_id;
user_id               product_count
--------------------  --------------------
1                     4
2                     1
3                     3
``````

The result shows that the user with userid = 1 has 4 products, userid = 2 has 1 product and user_id = 3 has 3 products. You can verify this by manually calculating the number of products from the following query.

``````sqlite> select * from products;
id                    price                 name        user_id
--------------------  --------------------  ----------  ----------
1                     10                    Rock        1
2                     20                    Sand        1
3                     30                    Carrot      1
4                     40                    Hammer      1
5                     40                    Tree        2
6                     80                    Cage        3
7                     80                    Cage Wire   3
8                     80                    Camel Rope  3
``````

You can see that the user with userid has only one product whereas users with userid 1 and 3 have 4 and 3 products respectively.

# Having Clause

You can filter groups to include and exclude by using Having clause.

``````sqlite> select user_id, count(*) as count from products group by user_id having count(*) > 2;
user_id               count
--------------------  --------------------
1                     4
3                     3
``````

The having clause filters on the group and finds only the rows that has more than 2 products for the user. You can verify this by looking at the records.

``````sqlite> select * from products;
id                    price                 name        user_id
--------------------  --------------------  ----------  ----------
1                     10                    Rock        1
2                     20                    Sand        1
3                     30                    Carrot      1
4                     40                    Hammer      1
5                     40                    Tree        2
6                     80                    Cage        3
7                     80                    Cage Wire   3
8                     80                    Camel Rope  3
``````

You see there is only one user with user_id = 2 with the number of products less than or equal to 2.

# Summary

In this article you learned how to use the GROUP BY and HAVIN clauses to perform aggregate functions on groups of data.

# Related Articles

Create your own user feedback survey