CODEWITHSUNDEEP
sql-server
WanYee Chin
WanYee Chin

Reputation: 3

Show multiple rows and columns using subquery under SELECT clause

I want to show the number of clerks and managers in each department.

Here is my code:

SELECT DISTINCT 
    deptno AS 'DEPARTMENT NUMBER',
    (SELECT COUNT(*) 
     FROM EMP
     WHERE JOB = 'CLERK'
     GROUP BY DEPTNO) AS 'NUMBER OF CLERKS',
    (SELECT COUNT(*) 
     FROM EMP
     WHERE JOB = 'MANAGER'
     GROUP BY DEPTNO) AS 'NUMBER OF MANAGER'
FROM 
    EMP
GROUP BY 
    deptno

Below is the error returned:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I wanted to show the result like this:

+-------------------+------------------+---------------------+
| DEPARTMENT NUMBER | NUMBER OF CLERKS | NUUMBER OF MANAGERS |
+-------------------+------------------+---------------------+
| 10                | 1                |    1                |
| 20                | 2                |    1                |
| 30                | 1                |    1                |
+-------------------+------------------+---------------------+

Upvotes: 0

Views: 42

Answers (2)

hkravitz
hkravitz

Reputation: 1385

you can also do it using PIVOT

    DECLARE @EMP table (Dep_No INT, Job VARCHAR(20))
    INSERT INTO @EMP
    VALUES (10,'clerk'),
           (10,'clerk'),
           (10,'clerk'),
           (10,'Manager'),
           (20,'clerk'),
           (20,'clerk'),
           (20,'clerk'),
           (20,'clerk'),
           (20,'clerk'),
           (20,'clerk'),
           (20,'Manager'),
           (20,'Manager'),
           (30,'clerk'),
           (20,'Manager'),
           (30,'clerk')

    SELECT Dep_No , [clerk] Number_Of_Clerks ,[Manager] Number_of_Managers 
     FROM @EMP
        PIVOT
            (
            COUNT(Job) FOR Job in ([clerk], [Manager])
            ) W

Upvotes: 0

James Z
James Z

Reputation: 12317

This kind of calculation is usually easiest to do using sum + case with something like this:

SELECT
    deptno,
    sum (case when JOB = 'CLERK' then 1 else 0 end) AS [NUMBER OF CLERKS],
    sum (case when JOB = 'MANAGER' then 1 else 0 end) AS [NUMBER OF MANAGERS]
FROM 
    EMP
GROUP BY 
    deptno

Upvotes: 1

Related Questions