SQL Basics : Grouping Data

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


Ace the Technical Interview

  • Easily find the gaps in your knowledge
  • Get customized lessons based on where you are
  • Take consistent action everyday
  • Builtin accountability to keep you on track
  • You will solve bigger problems over time
  • Get the job of your dreams

Take the 30 Day Coding Skills Challenge

Gain confidence to attend the interview

No spam ever. Unsubscribe anytime.