How to count records in SQL

Welcome to SO.

This problem is quite simple to solve. The steps would be as follows:

1. Join the Departments table to the Employees table on the Dept_ID column.

2. SELECT the Dept_ID and Count() and GROUP BY the Dept_ID field.

3. In order to return Departments without employees you need to LEFT JOIN this aggregation to the Departments table on the Dept_ID column.

4. In order to return the value of 0 for departments without employees, use the ISNULL() function.

Please see the below sample script using your schema. Note that this script is written in T-SQL, as you did not mention your server type.

create table #employees 
(
    Emp_ID int,
    Name varchar(50),
    Dept_ID int,
);

create table #departments
(
    Dept_ID int,
    Dept_Name varchar(50)
);

insert into #employees
select 1, 'Pim', 1
union all
select 2, 'Salma', 2;

insert into #departments
select 1, 'IT'
union all
select 2, 'Marketing'
union all
select 3, 'Design'

select
    d1.Dept_Name
    ,isnull(d2.EmployeeCount, 0) as EmployeeCount
from
    #departments d1
left join
    (
        select
            d.Dept_ID
            ,count(e.Dept_ID) as EmployeeCount
        from
            #departments d
        join
            #employees e
            on e.Dept_ID = d.Dept_ID
        group by
            d.Dept_ID
    )
    d2
    on d2.Dept_ID = d1.Dept_ID

drop table #employees
drop table #departments