CODEWITHSUNDEEP
sql-serversubqueryaggregate-functions
KVASS
KVASS

Reputation: 63

Referencing outer table in an aggregate function in a subquery

I'm looking for a solution to particular query problem. I have a table Departments and table Employees designed like that:

Departments                      Employees
=====================            ============================
ID | Name                        ID | Name | Surname | DeptID
---------------------            ----------------------------
1  | ADMINISTRATION              1  | X    | Y       | 2
2  | IT                          2  | Z    | Z       | 1
3  | ADVERTISEMENT               3  | O    | O       | 1
                                 4  | A    | B       | 3

I'd like to get list of all departments whose number of employees is smaller than number of employees working in Administration. That was one of my ideas, but it did not work:

select * from Departments as Depts where Depts.ID in
(select Employees.ID from Employees group by Employees.ID
 having count(Employees.ID) < count(case when Depts.Name='ADMINISTRATION' then 1 end));

Upvotes: 0

Views: 38

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

Try this,

declare @Departments table (ID int, Name varchar(50))                      
insert into @Departments          
values           
(1 ,'ADMINISTRATION')             
,(2 ,'IT')              
,(3 ,'ADVERTISEMENT')   


declare @Employees table (ID int, Name varchar(50)
,Surname varchar(50),DeptID int)          
insert into @Employees
values
(1 ,'X','Y',2)
,(2 ,'Z','Z',1)
,(3 ,'O','O',1)
,(4 ,'A','B',3)      


;

WITH CTE
AS (
    SELECT *
        ,row_number() OVER (
            PARTITION BY deptid ORDER BY id
            ) rn
    FROM @Employees
    WHERE deptid <> 1
    )
SELECT *
FROM cte
WHERE rn < (
        SELECT count(id) admincount
        FROM @Employees
        WHERE DeptID = 1
        )

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using GROUP BY and HAVING:

SELECT
    d.ID, d.Name
FROM Departments d
LEFT JOIN Employees e
    ON e.DeptID = d.ID
GROUP BY d.ID, d.Name
HAVING
    COUNT(e.ID) < (SELECT COUNT(*) FROM Employees WHERE DeptID = 1)

Upvotes: 1

Related Questions