Learn From - Interactive SQL


Next Chapter »

SQL Subquery and Aggregate Function


SQL using simple Subquery using 2 Tables "Employee" and "Department" Table.

Below is shown the "Employees" table:

EmployeeID EmployeeName DateOfBirth DesignationID DeptID PhoneNo City Salary
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:

DeptID DeptName
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 D.DeptID, D.DeptName,
(Select COUNT(EmployeeID) from Employees
Where Employees.DeptID = D.DeptID ) as EmployeeCount
From Department D

Note: Using aggregate function COUNT in the Subquery and passing D.DeptID as the Argument to the Subquery.

SQL in Text Editor:








Next Chapter »