Grouping Data
In this chapter you will 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 user_id = 1 has 4 products, user_id = 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 user_id has only one product whereas users with user_id 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 HAVING clauses to perform aggregate functions on groups of data.