It is time for aggregating data, using the GROUP BY keyword.

Hint: I will be using the emp/dept tables used by Oracle for demo in all demonstrations. I recommend Live SQL for playing, it is a great place to test SQL and learn from others. You can also sign up for a database for free (for a short time) on Oracle’s cloud. I recommend beginning with Live SQL and moving to paid services when you are ready,

Hint II: You can run the code in here by going through the little tutorial I created.

In here we will use the hr.employees table that lists data of employees including the department they work in. From this we want a small report showing the number of employees that works for each department.

For this we want to count each employee and sum it up by department ID.

Lets first use this to show all rows in the hr.employees table:

select * from hr.employees;

Now we’ll use the function count(*) to count up all the rows. Without anything else it would just give us a count of all rows in the table. Since we want to get the number of rows (employees) per department we use the keyword “group by” to specify that we want data to be grouped by department id.

Thus we’ll use this SQL:

select department_id
     , count(*)
  from hr.employees
 group by department_id;

DEPARTMENT_ID COUNT(*)
50            45
40             1
110            2
90             3
30             6
70             1
–              1
10             1
20             2
60             5
100            6
80            34

When we run this we will get a report like this:

Pretty nice, now we know how many people there are in each department. JUst having the ID is not all that nice, but it can be found by looking in the department table. In a future post, I’ll talk about how to combine the two tables in one SQL.

The observant reader has noticed that there is one employee that does not belong to any department, that row has an ID of “-“. Can you figure out who it is by applying the last episode of BTB?  It is actually not a dash in the column, but it is a NULL. That is database speak for no value has been given. That too will be a future post.