CODEWITHSUNDEEP
sqlsql-server
Edmond Liu
Edmond Liu

Reputation: 21

SQL Querying Using Subquery

Department (dNumber, dName)

Employee (SSN, eName, salary, /dNumber/)

Project (pNumber, pName, pLocation)

WorksOn (/SSN/, /pNumber/, hours)

These are the tables I am working with. I want to list all department numbers and names where more than 5 employees work, and count the number of those employees whose salaries are greater than 40,000. I want to practice using subqueries.

Here is what I wrote:

SELECT T.dNumber, T.dName, COUNT(T.SSN)
FROM 
(
SELECT d.dNumber, d.dName, e.SSN
FROM Department d, Employee e
WHERE e.salary > 40000 AND d.dNumber = e.dNo
) as T
GROUP BY dNumber, dName
HAVING COUNT(T.SSN) > 5;

But it looks and feels redundant. It's almost as if I don't really need to use subqueries. Any ideas?

Thank you!

Upvotes: 1

Views: 86

Answers (2)

knockout
knockout

Reputation: 149


SELECT
    d1.dNumber,
    d1.dName,
    ISNULL (SUM (CASE WHEN e1.SSN IS NOT NULL THEN 1 ELSE 0 END), 0) AS EmployeeCount
FROM
(
    SELECT
        Department.dNumber,
        Department.dName
    FROM
        Employee
    JOIN
        Department
    ON
        Employee.dNumber = Department.dNumber
    GROUP BY
        Employee.dNumber
    HAVING
        COUNT(*) > 5
) AS d
LEFT JOIN
    Employee e1
ON
    e1.dNumber = d1.dNumber AND
    e1.Salary > 40000
GROUP BY
    d1.dNumber,
    d1.dName

Upvotes: 0

Uriil
Uriil

Reputation: 12618

I think below query will return you expected result(it's for MySql, in case of MSSQL you will need to replace IF with CASE condition):

SELECT T.dNumber, T.dName, COUNT(T.SSN) AS TotalEmployees, sum(IF(T.salary > 4000, 1, 0)) AS EmployeesOverFourty
FROM Department d
INNER JOIN Employee e ON d.dNumber = e.dNo
GROUP BY dNumber, dName
HAVING TotalEmployees > 5;

Upvotes: 2

Related Questions