SQL using simple INNER JOIN on 2 Tables Employee and Department Table.
Below is shown the "Employees" table:
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
343-123-2002 |
New Orleans |
950.00 |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
232-344-8755 |
San Francisco |
800.00 |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
435-657-9068 |
New York |
700.00 |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
323-454-7656 |
Salt Lake City |
600.00 |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
634-465-8046 |
New York |
500.00 |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
434-434-1254 |
Houston |
400.00 |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
565-782-1223 |
Long Island |
650.00 |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
954-652-2111 |
Salt Lake City |
600.00 |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
323-243-1249 |
San Francisco |
750.00 |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
543-765-4653 |
Salt Lake City |
450.00 |
11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
443-324-6522 |
Austin |
500.00 |
Below is shown the "Department" table:
1 |
Finance |
2 |
Development |
3 |
Testing |
4 |
Delivery |
5 |
Human Resources |
6 |
Administration |
In the "Employees" table EmployeeID is the Primary Key and DeptID is the Foreign Key.
In the "Department" table DeptID is the Primary Key
To retrieve the Count of Employees for every Department.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select Department.DeptID, Department.DeptName,
COUNT(Employees.EmployeeID) as EmployeeCount
From Department
INNER JOIN Employees on Department.DeptID = Employees.DeptID
GROUP BY Department.DeptID, Department.DeptName;
Note: We first join the Department and Employees table using the Inner Join. And also for the aggregate function
Count to work, need to use Group By on all columns apart from the column used for Aggreagate.