SQL Inner Join, Order By and Group By

Let's play in the database console.

rails dbconsole

List all the tables.

> .tables
ar_internal_metadata  products              tasks               
categories            projects              users               
categories_products   schema_migrations   

Turn on the headers so that we can see the headers for each column in the result.

> .headers on

Let's list all the tasks in the database.

sqlite> select * from tasks;
id          name            complete    created_at                  updated_at                  project_id  priority  
----------  --------------  ----------  --------------------------  --------------------------  ----------  ----------
1           Get rich quick  f           2016-03-16 19:07:26.558227  2016-03-16 19:07:26.558227  1           4         
2           Write a book    t           2016-03-16 19:07:26.560890  2016-03-16 19:07:26.560890  1           5         
3           Buy a puppy     f           2016-03-16 19:07:26.564746  2016-03-16 19:07:26.564746  2           9         
4           Dance in the r  t           2016-03-16 19:07:26.566557  2016-03-16 19:07:26.566557  2           7         
5           Dance in the s  t           2016-03-16 19:07:26.568377  2016-03-16 19:07:26.568377  2           6         
6           Walk on water   t           2016-03-16 19:07:26.570329  2016-03-16 19:07:26.570329  2           7         
7           Just Blog It    t           2016-03-16 19:07:26.572176  2016-03-16 19:07:26.572176  2           5         
8           Play tennis     t           2016-03-16 19:07:26.573898  2016-03-16 19:07:26.573898  2           4         
9           Make fun of Do  t           2016-03-16 19:07:26.575743  2016-03-16 19:07:26.575743  2           3         
10          Walk on moon    t           2016-03-16 19:07:26.577819  2016-03-16 19:07:26.577819  2           2 

How can we get the count of tasks for each project?

sqlite> select project_id, count(*) as TaskCount from tasks group by project_id;
project_id  TaskCount 
----------  ----------
1           2         
2           8    

The above SQL query used the group by to get the job done. We can also use join to accomplish the same as follows:

select projects.*, COUNT(tasks.project_id) as total_tasks
   ...> from projects
   ...> join tasks on tasks.project_id = projects.id
   ...> group by projects.id;
id          name             created_at                  updated_at                  total_tasks
----------  ---------------  --------------------------  --------------------------  -----------
1           Wealth Building  2016-03-16 19:07:26.505001  2016-03-16 19:07:26.505001  2          
2           Be Happy         2016-03-16 19:07:26.562468  2016-03-16 19:07:26.562468  8 

What's the point of using both group by and the join? We can use left join if we want tasks with 0 count.

sqlite> select projects.*, COUNT(tasks.project_id) as total_tasks
   ...> from projects
   ...> left join tasks on tasks.project_id = projects.id
   ...> group by projects.id
   ...> order by count(tasks.project_id);
id          name        created_at                  updated_at                  total_tasks
----------  ----------  --------------------------  --------------------------  -----------
3           Cooking     2016-03-16 20:06:26.951760  2016-03-16 20:06:26.951760  0          
1           Wealth Bui  2016-03-16 19:07:26.505001  2016-03-16 19:07:26.505001  2          
2           Be Happy    2016-03-16 19:07:26.562468  2016-03-16 19:07:26.562468  8  

If we don't use the order by we will get the results that is ordered by id not by total tasks.

sqlite> select projects.*, COUNT(tasks.project_id) as total_tasks
   ...>  from projects
   ...>  left join tasks on tasks.project_id = projects.id
   ...>  group by projects.id;
id          name             created_at                  updated_at                  total_tasks
----------  ---------------  --------------------------  --------------------------  -----------
1           Wealth Building  2016-03-16 19:07:26.505001  2016-03-16 19:07:26.505001  2          
2           Be Happy         2016-03-16 19:07:26.562468  2016-03-16 19:07:26.562468  8          
3           Cooking          2016-03-16 20:06:26.951760  2016-03-16 20:06:26.951760  0 

Summary

In this article, you learned how to use order by, group by, join and inner join.


Related Articles


Create your own user feedback survey