CODEWITHSUNDEEP
sqldatabaseoracle-database
Xzya
Xzya

Reputation: 347

SELECT specific information

My tables are structured like this (there are more values in the tables but I only wrote the ones relevant to this): 

Department(dep_id, dep_name)  
Employee(dep_id)

I need to display dep_name and the number of employees in every department, except once specific department (let's call it DepX) and only the departments with more than one employee. I tried multiple methods to solve this but none of them worked. Some methods I tried:

SELECT department.dep_name, COUNT(employee.dep_id) AS NumberOfEmployees FROM employee
INNER JOIN department ON employee.dep_id=department.dep_id
WHERE dep_name<>'DepX'
GROUP BY dep_id
HAVING COUNT(employee.dep_id) > 1;

SELECT dep_name FROM department
WHERE dep_name <>'DepX'
UNION
SELECT COUNT(*) FROM employee
WHERE COUNT(*) > 1
GROUP BY dep_id;

I can't figure this out. Thanks!

Upvotes: 1

Views: 105

Answers (3)

Rohit Tiwari
Rohit Tiwari

Reputation: 822

SELECT department.dep_name, COUNT(employee.dep_id) AS NumberOfEmployees FROM employee
INNER JOIN department ON employee.dep_id=department.dep_id
WHERE department.dep_name not in('DepX')
GROUP BY department.dep_name
HAVING COUNT(employee.dep_id) > 1;

update your table alias per your need

Upvotes: 1

LHA
LHA

Reputation: 9655

TEST this. This query help you return not only dept_name, it can return all fields from Department if you want:

SELECT d.*, A.numOfEmployees
FROM Department d,
     (
    SELECT e.dep_id, COUNT(*) numOfEmployees
    FROM Employee e
    GROUP BY e.dep_id
    HAVING COUNT(*) > 1
     ) A
WHERE d.dep_id = A.dep_id
AND d.dep_name != 'DepX'

Upvotes: 0

D Stanley
D Stanley

Reputation: 152521

The first example does now work because you're including dep_name in your results without an aggregation but not grouping on it.

You can either use the department name in your grouping instead of the ID:

SELECT department.dep_name, COUNT(employee.dep_id) AS NumberOfEmployees FROM employee
INNER JOIN department ON employee.dep_id=department.dep_id
WHERE dep_name<>'DepX'
GROUP BY department.dep_name
HAVING COUNT(employee.dep_id) > 1;

or do the COUNT in a subquery:

SELECT department.dep_name, 
       e.NumberOfEmployees 
FROM department
INNER JOIN (SELECT dep_id, 
                   COUNT(*) NumberOfEmployees 
            FROM employee
            GROUP BY dept_id
            HAVING COUNT(dept_id) > 1    
           ) e
ON department.dep_id = e.dep_id
WHERE dep_name<>'DepX'

Upvotes: 1

Related Questions